1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    ensure_list,
  30    seq_get,
  31    split_num_words,
  32    subclasses,
  33)
  34from sqlglot.tokens import Token
  35
  36if t.TYPE_CHECKING:
  37    from sqlglot.dialects.dialect import DialectType
  38
  39E = t.TypeVar("E", bound="Expression")
  40
  41
  42class _Expression(type):
  43    def __new__(cls, clsname, bases, attrs):
  44        klass = super().__new__(cls, clsname, bases, attrs)
  45
  46        # When an Expression class is created, its key is automatically set to be
  47        # the lowercase version of the class' name.
  48        klass.key = clsname.lower()
  49
  50        # This is so that docstrings are not inherited in pdoc
  51        klass.__doc__ = klass.__doc__ or ""
  52
  53        return klass
  54
  55
  56class Expression(metaclass=_Expression):
  57    """
  58    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  59    context, such as its child expressions, their names (arg keys), and whether a given child expression
  60    is optional or not.
  61
  62    Attributes:
  63        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  64            and representing expressions as strings.
  65        arg_types: determines what arguments (child nodes) are supported by an expression. It
  66            maps arg keys to booleans that indicate whether the corresponding args are optional.
  67
  68    Example:
  69        >>> class Foo(Expression):
  70        ...     arg_types = {"this": True, "expression": False}
  71
  72        The above definition informs us that Foo is an Expression that requires an argument called
  73        "this" and may also optionally receive an argument called "expression".
  74
  75    Args:
  76        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  77        parent: a reference to the parent expression (or None, in case of root expressions).
  78        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  79            uses to refer to it.
  80        comments: a list of comments that are associated with a given expression. This is used in
  81            order to preserve comments when transpiling SQL code.
  82        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  83            optimizer, in order to enable some transformations that require type information.
  84    """
  85
  86    key = "expression"
  87    arg_types = {"this": True}
  88    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  89
  90    def __init__(self, **args: t.Any):
  91        self.args: t.Dict[str, t.Any] = args
  92        self.parent: t.Optional[Expression] = None
  93        self.arg_key: t.Optional[str] = None
  94        self.comments: t.Optional[t.List[str]] = None
  95        self._type: t.Optional[DataType] = None
  96        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  97        self._hash: t.Optional[int] = None
  98
  99        for arg_key, value in self.args.items():
 100            self._set_parent(arg_key, value)
 101
 102    def __eq__(self, other) -> bool:
 103        return type(self) is type(other) and hash(self) == hash(other)
 104
 105    @property
 106    def hashable_args(self) -> t.Any:
 107        args = (self.args.get(k) for k in self.arg_types)
 108
 109        return tuple(
 110            (tuple(_norm_arg(a) for a in arg) if arg else None)
 111            if type(arg) is list
 112            else (_norm_arg(arg) if arg is not None and arg is not False else None)
 113            for arg in args
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self):
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self):
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def name(self) -> str:
 200        return self.text("this")
 201
 202    @property
 203    def alias_or_name(self):
 204        return self.alias or self.name
 205
 206    @property
 207    def output_name(self):
 208        """
 209        Name of the output column if this expression is a selection.
 210
 211        If the Expression has no output name, an empty string is returned.
 212
 213        Example:
 214            >>> from sqlglot import parse_one
 215            >>> parse_one("SELECT a").expressions[0].output_name
 216            'a'
 217            >>> parse_one("SELECT b AS c").expressions[0].output_name
 218            'c'
 219            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 220            ''
 221        """
 222        return ""
 223
 224    @property
 225    def type(self) -> t.Optional[DataType]:
 226        return self._type
 227
 228    @type.setter
 229    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 230        if dtype and not isinstance(dtype, DataType):
 231            dtype = DataType.build(dtype)
 232        self._type = dtype  # type: ignore
 233
 234    @property
 235    def meta(self) -> t.Dict[str, t.Any]:
 236        if self._meta is None:
 237            self._meta = {}
 238        return self._meta
 239
 240    def __deepcopy__(self, memo):
 241        copy = self.__class__(**deepcopy(self.args))
 242        if self.comments is not None:
 243            copy.comments = deepcopy(self.comments)
 244
 245        if self._type is not None:
 246            copy._type = self._type.copy()
 247
 248        if self._meta is not None:
 249            copy._meta = deepcopy(self._meta)
 250
 251        return copy
 252
 253    def copy(self):
 254        """
 255        Returns a deep copy of the expression.
 256        """
 257        new = deepcopy(self)
 258        new.parent = self.parent
 259        return new
 260
 261    def append(self, arg_key, value):
 262        """
 263        Appends value to arg_key if it's a list or sets it as a new list.
 264
 265        Args:
 266            arg_key (str): name of the list expression arg
 267            value (Any): value to append to the list
 268        """
 269        if not isinstance(self.args.get(arg_key), list):
 270            self.args[arg_key] = []
 271        self.args[arg_key].append(value)
 272        self._set_parent(arg_key, value)
 273
 274    def set(self, arg_key, value):
 275        """
 276        Sets `arg_key` to `value`.
 277
 278        Args:
 279            arg_key (str): name of the expression arg.
 280            value: value to set the arg to.
 281        """
 282        self.args[arg_key] = value
 283        self._set_parent(arg_key, value)
 284
 285    def _set_parent(self, arg_key, value):
 286        if hasattr(value, "parent"):
 287            value.parent = self
 288            value.arg_key = arg_key
 289        elif type(value) is list:
 290            for v in value:
 291                if hasattr(v, "parent"):
 292                    v.parent = self
 293                    v.arg_key = arg_key
 294
 295    @property
 296    def depth(self):
 297        """
 298        Returns the depth of this tree.
 299        """
 300        if self.parent:
 301            return self.parent.depth + 1
 302        return 0
 303
 304    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 305        """Yields the key and expression for all arguments, exploding list args."""
 306        for k, vs in self.args.items():
 307            if type(vs) is list:
 308                for v in vs:
 309                    if hasattr(v, "parent"):
 310                        yield k, v
 311            else:
 312                if hasattr(vs, "parent"):
 313                    yield k, vs
 314
 315    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 316        """
 317        Returns the first node in this tree which matches at least one of
 318        the specified types.
 319
 320        Args:
 321            expression_types: the expression type(s) to match.
 322
 323        Returns:
 324            The node which matches the criteria or None if no such node was found.
 325        """
 326        return next(self.find_all(*expression_types, bfs=bfs), None)
 327
 328    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 329        """
 330        Returns a generator object which visits all nodes in this tree and only
 331        yields those that match at least one of the specified expression types.
 332
 333        Args:
 334            expression_types: the expression type(s) to match.
 335
 336        Returns:
 337            The generator object.
 338        """
 339        for expression, *_ in self.walk(bfs=bfs):
 340            if isinstance(expression, expression_types):
 341                yield expression
 342
 343    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 344        """
 345        Returns a nearest parent matching expression_types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349
 350        Returns:
 351            The parent node.
 352        """
 353        ancestor = self.parent
 354        while ancestor and not isinstance(ancestor, expression_types):
 355            ancestor = ancestor.parent
 356        return t.cast(E, ancestor)
 357
 358    @property
 359    def parent_select(self):
 360        """
 361        Returns the parent select statement.
 362        """
 363        return self.find_ancestor(Select)
 364
 365    @property
 366    def same_parent(self):
 367        """Returns if the parent is the same class as itself."""
 368        return type(self.parent) is self.__class__
 369
 370    def root(self) -> Expression:
 371        """
 372        Returns the root expression of this tree.
 373        """
 374        expression = self
 375        while expression.parent:
 376            expression = expression.parent
 377        return expression
 378
 379    def walk(self, bfs=True, prune=None):
 380        """
 381        Returns a generator object which visits all nodes in this tree.
 382
 383        Args:
 384            bfs (bool): if set to True the BFS traversal order will be applied,
 385                otherwise the DFS traversal will be used instead.
 386            prune ((node, parent, arg_key) -> bool): callable that returns True if
 387                the generator should stop traversing this branch of the tree.
 388
 389        Returns:
 390            the generator object.
 391        """
 392        if bfs:
 393            yield from self.bfs(prune=prune)
 394        else:
 395            yield from self.dfs(prune=prune)
 396
 397    def dfs(self, parent=None, key=None, prune=None):
 398        """
 399        Returns a generator object which visits all nodes in this tree in
 400        the DFS (Depth-first) order.
 401
 402        Returns:
 403            The generator object.
 404        """
 405        parent = parent or self.parent
 406        yield self, parent, key
 407        if prune and prune(self, parent, key):
 408            return
 409
 410        for k, v in self.iter_expressions():
 411            yield from v.dfs(self, k, prune)
 412
 413    def bfs(self, prune=None):
 414        """
 415        Returns a generator object which visits all nodes in this tree in
 416        the BFS (Breadth-first) order.
 417
 418        Returns:
 419            The generator object.
 420        """
 421        queue = deque([(self, self.parent, None)])
 422
 423        while queue:
 424            item, parent, key = queue.popleft()
 425
 426            yield item, parent, key
 427            if prune and prune(item, parent, key):
 428                continue
 429
 430            for k, v in item.iter_expressions():
 431                queue.append((v, item, k))
 432
 433    def unnest(self):
 434        """
 435        Returns the first non parenthesis child or self.
 436        """
 437        expression = self
 438        while type(expression) is Paren:
 439            expression = expression.this
 440        return expression
 441
 442    def unalias(self):
 443        """
 444        Returns the inner expression if this is an Alias.
 445        """
 446        if isinstance(self, Alias):
 447            return self.this
 448        return self
 449
 450    def unnest_operands(self):
 451        """
 452        Returns unnested operands as a tuple.
 453        """
 454        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 455
 456    def flatten(self, unnest=True):
 457        """
 458        Returns a generator which yields child nodes who's parents are the same class.
 459
 460        A AND B AND C -> [A, B, C]
 461        """
 462        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 463            if not type(node) is self.__class__:
 464                yield node.unnest() if unnest else node
 465
 466    def __str__(self):
 467        return self.sql()
 468
 469    def __repr__(self):
 470        return self._to_s()
 471
 472    def sql(self, dialect: DialectType = None, **opts) -> str:
 473        """
 474        Returns SQL string representation of this tree.
 475
 476        Args:
 477            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 478            opts: other `sqlglot.generator.Generator` options.
 479
 480        Returns:
 481            The SQL string.
 482        """
 483        from sqlglot.dialects import Dialect
 484
 485        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 486
 487    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 488        indent = "" if not level else "\n"
 489        indent += "".join(["  "] * level)
 490        left = f"({self.key.upper()} "
 491
 492        args: t.Dict[str, t.Any] = {
 493            k: ", ".join(
 494                v._to_s(hide_missing=hide_missing, level=level + 1)
 495                if hasattr(v, "_to_s")
 496                else str(v)
 497                for v in ensure_list(vs)
 498                if v is not None
 499            )
 500            for k, vs in self.args.items()
 501        }
 502        args["comments"] = self.comments
 503        args["type"] = self.type
 504        args = {k: v for k, v in args.items() if v or not hide_missing}
 505
 506        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 507        right += ")"
 508
 509        return indent + left + right
 510
 511    def transform(self, fun, *args, copy=True, **kwargs):
 512        """
 513        Recursively visits all tree nodes (excluding already transformed ones)
 514        and applies the given transformation function to each node.
 515
 516        Args:
 517            fun (function): a function which takes a node as an argument and returns a
 518                new transformed node or the same node without modifications. If the function
 519                returns None, then the corresponding node will be removed from the syntax tree.
 520            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 521                modified in place.
 522
 523        Returns:
 524            The transformed tree.
 525        """
 526        node = self.copy() if copy else self
 527        new_node = fun(node, *args, **kwargs)
 528
 529        if new_node is None or not isinstance(new_node, Expression):
 530            return new_node
 531        if new_node is not node:
 532            new_node.parent = node.parent
 533            return new_node
 534
 535        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 536        return new_node
 537
 538    def replace(self, expression):
 539        """
 540        Swap out this expression with a new expression.
 541
 542        For example::
 543
 544            >>> tree = Select().select("x").from_("tbl")
 545            >>> tree.find(Column).replace(Column(this="y"))
 546            (COLUMN this: y)
 547            >>> tree.sql()
 548            'SELECT y FROM tbl'
 549
 550        Args:
 551            expression (Expression|None): new node
 552
 553        Returns:
 554            The new expression or expressions.
 555        """
 556        if not self.parent:
 557            return expression
 558
 559        parent = self.parent
 560        self.parent = None
 561
 562        replace_children(parent, lambda child: expression if child is self else child)
 563        return expression
 564
 565    def pop(self):
 566        """
 567        Remove this expression from its AST.
 568
 569        Returns:
 570            The popped expression.
 571        """
 572        self.replace(None)
 573        return self
 574
 575    def assert_is(self, type_):
 576        """
 577        Assert that this `Expression` is an instance of `type_`.
 578
 579        If it is NOT an instance of `type_`, this raises an assertion error.
 580        Otherwise, this returns this expression.
 581
 582        Examples:
 583            This is useful for type security in chained expressions:
 584
 585            >>> import sqlglot
 586            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 587            'SELECT x, z FROM y'
 588        """
 589        assert isinstance(self, type_)
 590        return self
 591
 592    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 593        """
 594        Checks if this expression is valid (e.g. all mandatory args are set).
 595
 596        Args:
 597            args: a sequence of values that were used to instantiate a Func expression. This is used
 598                to check that the provided arguments don't exceed the function argument limit.
 599
 600        Returns:
 601            A list of error messages for all possible errors that were found.
 602        """
 603        errors: t.List[str] = []
 604
 605        for k in self.args:
 606            if k not in self.arg_types:
 607                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 608        for k, mandatory in self.arg_types.items():
 609            v = self.args.get(k)
 610            if mandatory and (v is None or (isinstance(v, list) and not v)):
 611                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 612
 613        if (
 614            args
 615            and isinstance(self, Func)
 616            and len(args) > len(self.arg_types)
 617            and not self.is_var_len_args
 618        ):
 619            errors.append(
 620                f"The number of provided arguments ({len(args)}) is greater than "
 621                f"the maximum number of supported arguments ({len(self.arg_types)})"
 622            )
 623
 624        return errors
 625
 626    def dump(self):
 627        """
 628        Dump this Expression to a JSON-serializable dict.
 629        """
 630        from sqlglot.serde import dump
 631
 632        return dump(self)
 633
 634    @classmethod
 635    def load(cls, obj):
 636        """
 637        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 638        """
 639        from sqlglot.serde import load
 640
 641        return load(obj)
 642
 643
 644IntoType = t.Union[
 645    str,
 646    t.Type[Expression],
 647    t.Collection[t.Union[str, t.Type[Expression]]],
 648]
 649ExpOrStr = t.Union[str, Expression]
 650
 651
 652class Condition(Expression):
 653    def and_(self, *expressions, dialect=None, **opts):
 654        """
 655        AND this condition with one or multiple expressions.
 656
 657        Example:
 658            >>> condition("x=1").and_("y=1").sql()
 659            'x = 1 AND y = 1'
 660
 661        Args:
 662            *expressions (str | Expression): the SQL code strings to parse.
 663                If an `Expression` instance is passed, it will be used as-is.
 664            dialect (str): the dialect used to parse the input expression.
 665            opts (kwargs): other options to use to parse the input expressions.
 666
 667        Returns:
 668            And: the new condition.
 669        """
 670        return and_(self, *expressions, dialect=dialect, **opts)
 671
 672    def or_(self, *expressions, dialect=None, **opts):
 673        """
 674        OR this condition with one or multiple expressions.
 675
 676        Example:
 677            >>> condition("x=1").or_("y=1").sql()
 678            'x = 1 OR y = 1'
 679
 680        Args:
 681            *expressions (str | Expression): the SQL code strings to parse.
 682                If an `Expression` instance is passed, it will be used as-is.
 683            dialect (str): the dialect used to parse the input expression.
 684            opts (kwargs): other options to use to parse the input expressions.
 685
 686        Returns:
 687            Or: the new condition.
 688        """
 689        return or_(self, *expressions, dialect=dialect, **opts)
 690
 691    def not_(self):
 692        """
 693        Wrap this condition with NOT.
 694
 695        Example:
 696            >>> condition("x=1").not_().sql()
 697            'NOT x = 1'
 698
 699        Returns:
 700            Not: the new condition.
 701        """
 702        return not_(self)
 703
 704
 705class Predicate(Condition):
 706    """Relationships like x = y, x > 1, x >= y."""
 707
 708
 709class DerivedTable(Expression):
 710    @property
 711    def alias_column_names(self):
 712        table_alias = self.args.get("alias")
 713        if not table_alias:
 714            return []
 715        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 716        return [c.name for c in column_list]
 717
 718    @property
 719    def selects(self):
 720        alias = self.args.get("alias")
 721
 722        if alias:
 723            return alias.columns
 724        return []
 725
 726    @property
 727    def named_selects(self):
 728        return [select.output_name for select in self.selects]
 729
 730
 731class Unionable(Expression):
 732    def union(self, expression, distinct=True, dialect=None, **opts):
 733        """
 734        Builds a UNION expression.
 735
 736        Example:
 737            >>> import sqlglot
 738            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 739            'SELECT * FROM foo UNION SELECT * FROM bla'
 740
 741        Args:
 742            expression (str | Expression): the SQL code string.
 743                If an `Expression` instance is passed, it will be used as-is.
 744            distinct (bool): set the DISTINCT flag if and only if this is true.
 745            dialect (str): the dialect used to parse the input expression.
 746            opts (kwargs): other options to use to parse the input expressions.
 747        Returns:
 748            Union: the Union expression.
 749        """
 750        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 751
 752    def intersect(self, expression, distinct=True, dialect=None, **opts):
 753        """
 754        Builds an INTERSECT expression.
 755
 756        Example:
 757            >>> import sqlglot
 758            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 759            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 760
 761        Args:
 762            expression (str | Expression): the SQL code string.
 763                If an `Expression` instance is passed, it will be used as-is.
 764            distinct (bool): set the DISTINCT flag if and only if this is true.
 765            dialect (str): the dialect used to parse the input expression.
 766            opts (kwargs): other options to use to parse the input expressions.
 767        Returns:
 768            Intersect: the Intersect expression
 769        """
 770        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 771
 772    def except_(self, expression, distinct=True, dialect=None, **opts):
 773        """
 774        Builds an EXCEPT expression.
 775
 776        Example:
 777            >>> import sqlglot
 778            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 779            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 780
 781        Args:
 782            expression (str | Expression): the SQL code string.
 783                If an `Expression` instance is passed, it will be used as-is.
 784            distinct (bool): set the DISTINCT flag if and only if this is true.
 785            dialect (str): the dialect used to parse the input expression.
 786            opts (kwargs): other options to use to parse the input expressions.
 787        Returns:
 788            Except: the Except expression
 789        """
 790        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 791
 792
 793class UDTF(DerivedTable, Unionable):
 794    pass
 795
 796
 797class Cache(Expression):
 798    arg_types = {
 799        "with": False,
 800        "this": True,
 801        "lazy": False,
 802        "options": False,
 803        "expression": False,
 804    }
 805
 806
 807class Uncache(Expression):
 808    arg_types = {"this": True, "exists": False}
 809
 810
 811class Create(Expression):
 812    arg_types = {
 813        "with": False,
 814        "this": True,
 815        "kind": True,
 816        "expression": False,
 817        "exists": False,
 818        "properties": False,
 819        "replace": False,
 820        "unique": False,
 821        "volatile": False,
 822        "indexes": False,
 823        "no_schema_binding": False,
 824        "begin": False,
 825    }
 826
 827
 828class Describe(Expression):
 829    arg_types = {"this": True, "kind": False}
 830
 831
 832class Pragma(Expression):
 833    pass
 834
 835
 836class Set(Expression):
 837    arg_types = {"expressions": False}
 838
 839
 840class SetItem(Expression):
 841    arg_types = {
 842        "this": False,
 843        "expressions": False,
 844        "kind": False,
 845        "collate": False,  # MySQL SET NAMES statement
 846        "global": False,
 847    }
 848
 849
 850class Show(Expression):
 851    arg_types = {
 852        "this": True,
 853        "target": False,
 854        "offset": False,
 855        "limit": False,
 856        "like": False,
 857        "where": False,
 858        "db": False,
 859        "full": False,
 860        "mutex": False,
 861        "query": False,
 862        "channel": False,
 863        "global": False,
 864        "log": False,
 865        "position": False,
 866        "types": False,
 867    }
 868
 869
 870class UserDefinedFunction(Expression):
 871    arg_types = {"this": True, "expressions": False, "wrapped": False}
 872
 873
 874class CharacterSet(Expression):
 875    arg_types = {"this": True, "default": False}
 876
 877
 878class With(Expression):
 879    arg_types = {"expressions": True, "recursive": False}
 880
 881    @property
 882    def recursive(self) -> bool:
 883        return bool(self.args.get("recursive"))
 884
 885
 886class WithinGroup(Expression):
 887    arg_types = {"this": True, "expression": False}
 888
 889
 890class CTE(DerivedTable):
 891    arg_types = {"this": True, "alias": True}
 892
 893
 894class TableAlias(Expression):
 895    arg_types = {"this": False, "columns": False}
 896
 897    @property
 898    def columns(self):
 899        return self.args.get("columns") or []
 900
 901
 902class BitString(Condition):
 903    pass
 904
 905
 906class HexString(Condition):
 907    pass
 908
 909
 910class ByteString(Condition):
 911    pass
 912
 913
 914class Column(Condition):
 915    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
 916
 917    @property
 918    def table(self) -> str:
 919        return self.text("table")
 920
 921    @property
 922    def db(self) -> str:
 923        return self.text("db")
 924
 925    @property
 926    def catalog(self) -> str:
 927        return self.text("catalog")
 928
 929    @property
 930    def output_name(self) -> str:
 931        return self.name
 932
 933    @property
 934    def parts(self) -> t.List[Identifier]:
 935        """Return the parts of a column in order catalog, db, table, name."""
 936        return [part for part in reversed(list(self.args.values())) if part]
 937
 938    def to_dot(self) -> Dot:
 939        """Converts the column into a dot expression."""
 940        parts = self.parts
 941        parent = self.parent
 942
 943        while parent:
 944            if isinstance(parent, Dot):
 945                parts.append(parent.expression)
 946            parent = parent.parent
 947
 948        return Dot.build(parts)
 949
 950
 951class ColumnPosition(Expression):
 952    arg_types = {"this": False, "position": True}
 953
 954
 955class ColumnDef(Expression):
 956    arg_types = {
 957        "this": True,
 958        "kind": False,
 959        "constraints": False,
 960        "exists": False,
 961        "position": False,
 962    }
 963
 964
 965class AlterColumn(Expression):
 966    arg_types = {
 967        "this": True,
 968        "dtype": False,
 969        "collate": False,
 970        "using": False,
 971        "default": False,
 972        "drop": False,
 973    }
 974
 975
 976class RenameTable(Expression):
 977    pass
 978
 979
 980class SetTag(Expression):
 981    arg_types = {"expressions": True, "unset": False}
 982
 983
 984class Comment(Expression):
 985    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
 986
 987
 988class ColumnConstraint(Expression):
 989    arg_types = {"this": False, "kind": True}
 990
 991
 992class ColumnConstraintKind(Expression):
 993    pass
 994
 995
 996class AutoIncrementColumnConstraint(ColumnConstraintKind):
 997    pass
 998
 999
1000class CaseSpecificColumnConstraint(ColumnConstraintKind):
1001    arg_types = {"not_": True}
1002
1003
1004class CharacterSetColumnConstraint(ColumnConstraintKind):
1005    arg_types = {"this": True}
1006
1007
1008class CheckColumnConstraint(ColumnConstraintKind):
1009    pass
1010
1011
1012class CollateColumnConstraint(ColumnConstraintKind):
1013    pass
1014
1015
1016class CommentColumnConstraint(ColumnConstraintKind):
1017    pass
1018
1019
1020class CompressColumnConstraint(ColumnConstraintKind):
1021    pass
1022
1023
1024class DateFormatColumnConstraint(ColumnConstraintKind):
1025    arg_types = {"this": True}
1026
1027
1028class DefaultColumnConstraint(ColumnConstraintKind):
1029    pass
1030
1031
1032class EncodeColumnConstraint(ColumnConstraintKind):
1033    pass
1034
1035
1036class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1037    # this: True -> ALWAYS, this: False -> BY DEFAULT
1038    arg_types = {
1039        "this": False,
1040        "start": False,
1041        "increment": False,
1042        "minvalue": False,
1043        "maxvalue": False,
1044        "cycle": False,
1045    }
1046
1047
1048class InlineLengthColumnConstraint(ColumnConstraintKind):
1049    pass
1050
1051
1052class NotNullColumnConstraint(ColumnConstraintKind):
1053    arg_types = {"allow_null": False}
1054
1055
1056# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1057class OnUpdateColumnConstraint(ColumnConstraintKind):
1058    pass
1059
1060
1061class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1062    arg_types = {"desc": False}
1063
1064
1065class TitleColumnConstraint(ColumnConstraintKind):
1066    pass
1067
1068
1069class UniqueColumnConstraint(ColumnConstraintKind):
1070    arg_types: t.Dict[str, t.Any] = {}
1071
1072
1073class UppercaseColumnConstraint(ColumnConstraintKind):
1074    arg_types: t.Dict[str, t.Any] = {}
1075
1076
1077class PathColumnConstraint(ColumnConstraintKind):
1078    pass
1079
1080
1081class Constraint(Expression):
1082    arg_types = {"this": True, "expressions": True}
1083
1084
1085class Delete(Expression):
1086    arg_types = {"with": False, "this": False, "using": False, "where": False, "returning": False}
1087
1088    def delete(
1089        self,
1090        table: ExpOrStr,
1091        dialect: DialectType = None,
1092        copy: bool = True,
1093        **opts,
1094    ) -> Delete:
1095        """
1096        Create a DELETE expression or replace the table on an existing DELETE expression.
1097
1098        Example:
1099            >>> delete("tbl").sql()
1100            'DELETE FROM tbl'
1101
1102        Args:
1103            table: the table from which to delete.
1104            dialect: the dialect used to parse the input expression.
1105            copy: if `False`, modify this expression instance in-place.
1106            opts: other options to use to parse the input expressions.
1107
1108        Returns:
1109            Delete: the modified expression.
1110        """
1111        return _apply_builder(
1112            expression=table,
1113            instance=self,
1114            arg="this",
1115            dialect=dialect,
1116            into=Table,
1117            copy=copy,
1118            **opts,
1119        )
1120
1121    def where(
1122        self,
1123        *expressions: ExpOrStr,
1124        append: bool = True,
1125        dialect: DialectType = None,
1126        copy: bool = True,
1127        **opts,
1128    ) -> Delete:
1129        """
1130        Append to or set the WHERE expressions.
1131
1132        Example:
1133            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1134            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1135
1136        Args:
1137            *expressions: the SQL code strings to parse.
1138                If an `Expression` instance is passed, it will be used as-is.
1139                Multiple expressions are combined with an AND operator.
1140            append: if `True`, AND the new expressions to any existing expression.
1141                Otherwise, this resets the expression.
1142            dialect: the dialect used to parse the input expressions.
1143            copy: if `False`, modify this expression instance in-place.
1144            opts: other options to use to parse the input expressions.
1145
1146        Returns:
1147            Delete: the modified expression.
1148        """
1149        return _apply_conjunction_builder(
1150            *expressions,
1151            instance=self,
1152            arg="where",
1153            append=append,
1154            into=Where,
1155            dialect=dialect,
1156            copy=copy,
1157            **opts,
1158        )
1159
1160    def returning(
1161        self,
1162        expression: ExpOrStr,
1163        dialect: DialectType = None,
1164        copy: bool = True,
1165        **opts,
1166    ) -> Delete:
1167        """
1168        Set the RETURNING expression. Not supported by all dialects.
1169
1170        Example:
1171            >>> delete("tbl").returning("*", dialect="postgres").sql()
1172            'DELETE FROM tbl RETURNING *'
1173
1174        Args:
1175            expression: the SQL code strings to parse.
1176                If an `Expression` instance is passed, it will be used as-is.
1177            dialect: the dialect used to parse the input expressions.
1178            copy: if `False`, modify this expression instance in-place.
1179            opts: other options to use to parse the input expressions.
1180
1181        Returns:
1182            Delete: the modified expression.
1183        """
1184        return _apply_builder(
1185            expression=expression,
1186            instance=self,
1187            arg="returning",
1188            prefix="RETURNING",
1189            dialect=dialect,
1190            copy=copy,
1191            into=Returning,
1192            **opts,
1193        )
1194
1195
1196class Drop(Expression):
1197    arg_types = {
1198        "this": False,
1199        "kind": False,
1200        "exists": False,
1201        "temporary": False,
1202        "materialized": False,
1203        "cascade": False,
1204        "constraints": False,
1205        "purge": False,
1206    }
1207
1208
1209class Filter(Expression):
1210    arg_types = {"this": True, "expression": True}
1211
1212
1213class Check(Expression):
1214    pass
1215
1216
1217class Directory(Expression):
1218    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1219    arg_types = {"this": True, "local": False, "row_format": False}
1220
1221
1222class ForeignKey(Expression):
1223    arg_types = {
1224        "expressions": True,
1225        "reference": False,
1226        "delete": False,
1227        "update": False,
1228    }
1229
1230
1231class PrimaryKey(Expression):
1232    arg_types = {"expressions": True, "options": False}
1233
1234
1235class Unique(Expression):
1236    arg_types = {"expressions": True}
1237
1238
1239# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1240# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1241class Into(Expression):
1242    arg_types = {"this": True, "temporary": False, "unlogged": False}
1243
1244
1245class From(Expression):
1246    arg_types = {"expressions": True}
1247
1248
1249class Having(Expression):
1250    pass
1251
1252
1253class Hint(Expression):
1254    arg_types = {"expressions": True}
1255
1256
1257class JoinHint(Expression):
1258    arg_types = {"this": True, "expressions": True}
1259
1260
1261class Identifier(Expression):
1262    arg_types = {"this": True, "quoted": False}
1263
1264    @property
1265    def quoted(self):
1266        return bool(self.args.get("quoted"))
1267
1268    @property
1269    def hashable_args(self) -> t.Any:
1270        if self.quoted and any(char.isupper() for char in self.this):
1271            return (self.this, self.quoted)
1272        return self.this.lower()
1273
1274    @property
1275    def output_name(self):
1276        return self.name
1277
1278
1279class Index(Expression):
1280    arg_types = {
1281        "this": False,
1282        "table": False,
1283        "where": False,
1284        "columns": False,
1285        "unique": False,
1286        "primary": False,
1287        "amp": False,  # teradata
1288    }
1289
1290
1291class Insert(Expression):
1292    arg_types = {
1293        "with": False,
1294        "this": True,
1295        "expression": False,
1296        "returning": False,
1297        "overwrite": False,
1298        "exists": False,
1299        "partition": False,
1300        "alternative": False,
1301    }
1302
1303
1304class Returning(Expression):
1305    arg_types = {"expressions": True}
1306
1307
1308# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1309class Introducer(Expression):
1310    arg_types = {"this": True, "expression": True}
1311
1312
1313# national char, like n'utf8'
1314class National(Expression):
1315    pass
1316
1317
1318class LoadData(Expression):
1319    arg_types = {
1320        "this": True,
1321        "local": False,
1322        "overwrite": False,
1323        "inpath": True,
1324        "partition": False,
1325        "input_format": False,
1326        "serde": False,
1327    }
1328
1329
1330class Partition(Expression):
1331    arg_types = {"expressions": True}
1332
1333
1334class Fetch(Expression):
1335    arg_types = {"direction": False, "count": False}
1336
1337
1338class Group(Expression):
1339    arg_types = {
1340        "expressions": False,
1341        "grouping_sets": False,
1342        "cube": False,
1343        "rollup": False,
1344    }
1345
1346
1347class Lambda(Expression):
1348    arg_types = {"this": True, "expressions": True}
1349
1350
1351class Limit(Expression):
1352    arg_types = {"this": False, "expression": True}
1353
1354
1355class Literal(Condition):
1356    arg_types = {"this": True, "is_string": True}
1357
1358    @property
1359    def hashable_args(self) -> t.Any:
1360        return (self.this, self.args.get("is_string"))
1361
1362    @classmethod
1363    def number(cls, number) -> Literal:
1364        return cls(this=str(number), is_string=False)
1365
1366    @classmethod
1367    def string(cls, string) -> Literal:
1368        return cls(this=str(string), is_string=True)
1369
1370    @property
1371    def output_name(self):
1372        return self.name
1373
1374
1375class Join(Expression):
1376    arg_types = {
1377        "this": True,
1378        "on": False,
1379        "side": False,
1380        "kind": False,
1381        "using": False,
1382        "natural": False,
1383    }
1384
1385    @property
1386    def kind(self):
1387        return self.text("kind").upper()
1388
1389    @property
1390    def side(self):
1391        return self.text("side").upper()
1392
1393    @property
1394    def alias_or_name(self):
1395        return self.this.alias_or_name
1396
1397    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1398        """
1399        Append to or set the ON expressions.
1400
1401        Example:
1402            >>> import sqlglot
1403            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1404            'JOIN x ON y = 1'
1405
1406        Args:
1407            *expressions (str | Expression): the SQL code strings to parse.
1408                If an `Expression` instance is passed, it will be used as-is.
1409                Multiple expressions are combined with an AND operator.
1410            append (bool): if `True`, AND the new expressions to any existing expression.
1411                Otherwise, this resets the expression.
1412            dialect (str): the dialect used to parse the input expressions.
1413            copy (bool): if `False`, modify this expression instance in-place.
1414            opts (kwargs): other options to use to parse the input expressions.
1415
1416        Returns:
1417            Join: the modified join expression.
1418        """
1419        join = _apply_conjunction_builder(
1420            *expressions,
1421            instance=self,
1422            arg="on",
1423            append=append,
1424            dialect=dialect,
1425            copy=copy,
1426            **opts,
1427        )
1428
1429        if join.kind == "CROSS":
1430            join.set("kind", None)
1431
1432        return join
1433
1434    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1435        """
1436        Append to or set the USING expressions.
1437
1438        Example:
1439            >>> import sqlglot
1440            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1441            'JOIN x USING (foo, bla)'
1442
1443        Args:
1444            *expressions (str | Expression): the SQL code strings to parse.
1445                If an `Expression` instance is passed, it will be used as-is.
1446            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1447                Otherwise, this resets the expression.
1448            dialect (str): the dialect used to parse the input expressions.
1449            copy (bool): if `False`, modify this expression instance in-place.
1450            opts (kwargs): other options to use to parse the input expressions.
1451
1452        Returns:
1453            Join: the modified join expression.
1454        """
1455        join = _apply_list_builder(
1456            *expressions,
1457            instance=self,
1458            arg="using",
1459            append=append,
1460            dialect=dialect,
1461            copy=copy,
1462            **opts,
1463        )
1464
1465        if join.kind == "CROSS":
1466            join.set("kind", None)
1467
1468        return join
1469
1470
1471class Lateral(UDTF):
1472    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1473
1474
1475class MatchRecognize(Expression):
1476    arg_types = {
1477        "partition_by": False,
1478        "order": False,
1479        "measures": False,
1480        "rows": False,
1481        "after": False,
1482        "pattern": False,
1483        "define": False,
1484        "alias": False,
1485    }
1486
1487
1488# Clickhouse FROM FINAL modifier
1489# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1490class Final(Expression):
1491    pass
1492
1493
1494class Offset(Expression):
1495    arg_types = {"this": False, "expression": True}
1496
1497
1498class Order(Expression):
1499    arg_types = {"this": False, "expressions": True}
1500
1501
1502# hive specific sorts
1503# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1504class Cluster(Order):
1505    pass
1506
1507
1508class Distribute(Order):
1509    pass
1510
1511
1512class Sort(Order):
1513    pass
1514
1515
1516class Ordered(Expression):
1517    arg_types = {"this": True, "desc": True, "nulls_first": True}
1518
1519
1520class Property(Expression):
1521    arg_types = {"this": True, "value": True}
1522
1523
1524class AfterJournalProperty(Property):
1525    arg_types = {"no": True, "dual": False, "local": False}
1526
1527
1528class AlgorithmProperty(Property):
1529    arg_types = {"this": True}
1530
1531
1532class AutoIncrementProperty(Property):
1533    arg_types = {"this": True}
1534
1535
1536class BlockCompressionProperty(Property):
1537    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1538
1539
1540class CharacterSetProperty(Property):
1541    arg_types = {"this": True, "default": True}
1542
1543
1544class ChecksumProperty(Property):
1545    arg_types = {"on": False, "default": False}
1546
1547
1548class CollateProperty(Property):
1549    arg_types = {"this": True}
1550
1551
1552class DataBlocksizeProperty(Property):
1553    arg_types = {"size": False, "units": False, "min": False, "default": False}
1554
1555
1556class DefinerProperty(Property):
1557    arg_types = {"this": True}
1558
1559
1560class DistKeyProperty(Property):
1561    arg_types = {"this": True}
1562
1563
1564class DistStyleProperty(Property):
1565    arg_types = {"this": True}
1566
1567
1568class EngineProperty(Property):
1569    arg_types = {"this": True}
1570
1571
1572class ExecuteAsProperty(Property):
1573    arg_types = {"this": True}
1574
1575
1576class ExternalProperty(Property):
1577    arg_types = {"this": False}
1578
1579
1580class FallbackProperty(Property):
1581    arg_types = {"no": True, "protection": False}
1582
1583
1584class FileFormatProperty(Property):
1585    arg_types = {"this": True}
1586
1587
1588class FreespaceProperty(Property):
1589    arg_types = {"this": True, "percent": False}
1590
1591
1592class InputOutputFormat(Expression):
1593    arg_types = {"input_format": False, "output_format": False}
1594
1595
1596class IsolatedLoadingProperty(Property):
1597    arg_types = {
1598        "no": True,
1599        "concurrent": True,
1600        "for_all": True,
1601        "for_insert": True,
1602        "for_none": True,
1603    }
1604
1605
1606class JournalProperty(Property):
1607    arg_types = {"no": True, "dual": False, "before": False}
1608
1609
1610class LanguageProperty(Property):
1611    arg_types = {"this": True}
1612
1613
1614class LikeProperty(Property):
1615    arg_types = {"this": True, "expressions": False}
1616
1617
1618class LocationProperty(Property):
1619    arg_types = {"this": True}
1620
1621
1622class LockingProperty(Property):
1623    arg_types = {
1624        "this": False,
1625        "kind": True,
1626        "for_or_in": True,
1627        "lock_type": True,
1628        "override": False,
1629    }
1630
1631
1632class LogProperty(Property):
1633    arg_types = {"no": True}
1634
1635
1636class MaterializedProperty(Property):
1637    arg_types = {"this": False}
1638
1639
1640class MergeBlockRatioProperty(Property):
1641    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1642
1643
1644class NoPrimaryIndexProperty(Property):
1645    arg_types = {"this": False}
1646
1647
1648class OnCommitProperty(Property):
1649    arg_type = {"this": False}
1650
1651
1652class PartitionedByProperty(Property):
1653    arg_types = {"this": True}
1654
1655
1656class ReturnsProperty(Property):
1657    arg_types = {"this": True, "is_table": False, "table": False}
1658
1659
1660class RowFormatProperty(Property):
1661    arg_types = {"this": True}
1662
1663
1664class RowFormatDelimitedProperty(Property):
1665    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1666    arg_types = {
1667        "fields": False,
1668        "escaped": False,
1669        "collection_items": False,
1670        "map_keys": False,
1671        "lines": False,
1672        "null": False,
1673        "serde": False,
1674    }
1675
1676
1677class RowFormatSerdeProperty(Property):
1678    arg_types = {"this": True}
1679
1680
1681class SchemaCommentProperty(Property):
1682    arg_types = {"this": True}
1683
1684
1685class SerdeProperties(Property):
1686    arg_types = {"expressions": True}
1687
1688
1689class SetProperty(Property):
1690    arg_types = {"multi": True}
1691
1692
1693class SortKeyProperty(Property):
1694    arg_types = {"this": True, "compound": False}
1695
1696
1697class SqlSecurityProperty(Property):
1698    arg_types = {"definer": True}
1699
1700
1701class TableFormatProperty(Property):
1702    arg_types = {"this": True}
1703
1704
1705class TemporaryProperty(Property):
1706    arg_types = {"global_": True}
1707
1708
1709class TransientProperty(Property):
1710    arg_types = {"this": False}
1711
1712
1713class VolatilityProperty(Property):
1714    arg_types = {"this": True}
1715
1716
1717class WithDataProperty(Property):
1718    arg_types = {"no": True, "statistics": False}
1719
1720
1721class WithJournalTableProperty(Property):
1722    arg_types = {"this": True}
1723
1724
1725class Properties(Expression):
1726    arg_types = {"expressions": True}
1727
1728    NAME_TO_PROPERTY = {
1729        "ALGORITHM": AlgorithmProperty,
1730        "AUTO_INCREMENT": AutoIncrementProperty,
1731        "CHARACTER SET": CharacterSetProperty,
1732        "COLLATE": CollateProperty,
1733        "COMMENT": SchemaCommentProperty,
1734        "DEFINER": DefinerProperty,
1735        "DISTKEY": DistKeyProperty,
1736        "DISTSTYLE": DistStyleProperty,
1737        "ENGINE": EngineProperty,
1738        "EXECUTE AS": ExecuteAsProperty,
1739        "FORMAT": FileFormatProperty,
1740        "LANGUAGE": LanguageProperty,
1741        "LOCATION": LocationProperty,
1742        "PARTITIONED_BY": PartitionedByProperty,
1743        "RETURNS": ReturnsProperty,
1744        "ROW_FORMAT": RowFormatProperty,
1745        "SORTKEY": SortKeyProperty,
1746        "TABLE_FORMAT": TableFormatProperty,
1747    }
1748
1749    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1750
1751    # CREATE property locations
1752    # Form: schema specified
1753    #   create [POST_CREATE]
1754    #     table a [POST_NAME]
1755    #     (b int) [POST_SCHEMA]
1756    #     with ([POST_WITH])
1757    #     index (b) [POST_INDEX]
1758    #
1759    # Form: alias selection
1760    #   create [POST_CREATE]
1761    #     table a [POST_NAME]
1762    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1763    #     index (c) [POST_INDEX]
1764    class Location(AutoName):
1765        POST_CREATE = auto()
1766        POST_NAME = auto()
1767        POST_SCHEMA = auto()
1768        POST_WITH = auto()
1769        POST_ALIAS = auto()
1770        POST_EXPRESSION = auto()
1771        POST_INDEX = auto()
1772        UNSUPPORTED = auto()
1773
1774    @classmethod
1775    def from_dict(cls, properties_dict) -> Properties:
1776        expressions = []
1777        for key, value in properties_dict.items():
1778            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1779            if property_cls:
1780                expressions.append(property_cls(this=convert(value)))
1781            else:
1782                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1783
1784        return cls(expressions=expressions)
1785
1786
1787class Qualify(Expression):
1788    pass
1789
1790
1791# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1792class Return(Expression):
1793    pass
1794
1795
1796class Reference(Expression):
1797    arg_types = {"this": True, "expressions": False, "options": False}
1798
1799
1800class Tuple(Expression):
1801    arg_types = {"expressions": False}
1802
1803
1804class Subqueryable(Unionable):
1805    def subquery(self, alias=None, copy=True) -> Subquery:
1806        """
1807        Convert this expression to an aliased expression that can be used as a Subquery.
1808
1809        Example:
1810            >>> subquery = Select().select("x").from_("tbl").subquery()
1811            >>> Select().select("x").from_(subquery).sql()
1812            'SELECT x FROM (SELECT x FROM tbl)'
1813
1814        Args:
1815            alias (str | Identifier): an optional alias for the subquery
1816            copy (bool): if `False`, modify this expression instance in-place.
1817
1818        Returns:
1819            Alias: the subquery
1820        """
1821        instance = _maybe_copy(self, copy)
1822        return Subquery(
1823            this=instance,
1824            alias=TableAlias(this=to_identifier(alias)) if alias else None,
1825        )
1826
1827    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1828        raise NotImplementedError
1829
1830    @property
1831    def ctes(self):
1832        with_ = self.args.get("with")
1833        if not with_:
1834            return []
1835        return with_.expressions
1836
1837    @property
1838    def selects(self):
1839        raise NotImplementedError("Subqueryable objects must implement `selects`")
1840
1841    @property
1842    def named_selects(self):
1843        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1844
1845    def with_(
1846        self,
1847        alias,
1848        as_,
1849        recursive=None,
1850        append=True,
1851        dialect=None,
1852        copy=True,
1853        **opts,
1854    ):
1855        """
1856        Append to or set the common table expressions.
1857
1858        Example:
1859            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1860            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1861
1862        Args:
1863            alias (str | Expression): the SQL code string to parse as the table name.
1864                If an `Expression` instance is passed, this is used as-is.
1865            as_ (str | Expression): the SQL code string to parse as the table expression.
1866                If an `Expression` instance is passed, it will be used as-is.
1867            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1868            append (bool): if `True`, add to any existing expressions.
1869                Otherwise, this resets the expressions.
1870            dialect (str): the dialect used to parse the input expression.
1871            copy (bool): if `False`, modify this expression instance in-place.
1872            opts (kwargs): other options to use to parse the input expressions.
1873
1874        Returns:
1875            Select: the modified expression.
1876        """
1877        alias_expression = maybe_parse(
1878            alias,
1879            dialect=dialect,
1880            into=TableAlias,
1881            **opts,
1882        )
1883        as_expression = maybe_parse(
1884            as_,
1885            dialect=dialect,
1886            **opts,
1887        )
1888        cte = CTE(
1889            this=as_expression,
1890            alias=alias_expression,
1891        )
1892        return _apply_child_list_builder(
1893            cte,
1894            instance=self,
1895            arg="with",
1896            append=append,
1897            copy=copy,
1898            into=With,
1899            properties={"recursive": recursive or False},
1900        )
1901
1902
1903QUERY_MODIFIERS = {
1904    "match": False,
1905    "laterals": False,
1906    "joins": False,
1907    "pivots": False,
1908    "where": False,
1909    "group": False,
1910    "having": False,
1911    "qualify": False,
1912    "windows": False,
1913    "distribute": False,
1914    "sort": False,
1915    "cluster": False,
1916    "order": False,
1917    "limit": False,
1918    "offset": False,
1919    "lock": False,
1920    "sample": False,
1921}
1922
1923
1924class Table(Expression):
1925    arg_types = {
1926        "this": True,
1927        "alias": False,
1928        "db": False,
1929        "catalog": False,
1930        "laterals": False,
1931        "joins": False,
1932        "pivots": False,
1933        "hints": False,
1934        "system_time": False,
1935    }
1936
1937    @property
1938    def db(self) -> str:
1939        return self.text("db")
1940
1941    @property
1942    def catalog(self) -> str:
1943        return self.text("catalog")
1944
1945
1946# See the TSQL "Querying data in a system-versioned temporal table" page
1947class SystemTime(Expression):
1948    arg_types = {
1949        "this": False,
1950        "expression": False,
1951        "kind": True,
1952    }
1953
1954
1955class Union(Subqueryable):
1956    arg_types = {
1957        "with": False,
1958        "this": True,
1959        "expression": True,
1960        "distinct": False,
1961        **QUERY_MODIFIERS,
1962    }
1963
1964    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1965        """
1966        Set the LIMIT expression.
1967
1968        Example:
1969            >>> select("1").union(select("1")).limit(1).sql()
1970            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1971
1972        Args:
1973            expression (str | int | Expression): the SQL code string to parse.
1974                This can also be an integer.
1975                If a `Limit` instance is passed, this is used as-is.
1976                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1977            dialect (str): the dialect used to parse the input expression.
1978            copy (bool): if `False`, modify this expression instance in-place.
1979            opts (kwargs): other options to use to parse the input expressions.
1980
1981        Returns:
1982            Select: The limited subqueryable.
1983        """
1984        return (
1985            select("*")
1986            .from_(self.subquery(alias="_l_0", copy=copy))
1987            .limit(expression, dialect=dialect, copy=False, **opts)
1988        )
1989
1990    def select(
1991        self,
1992        *expressions: ExpOrStr,
1993        append: bool = True,
1994        dialect: DialectType = None,
1995        copy: bool = True,
1996        **opts,
1997    ) -> Union:
1998        """Append to or set the SELECT of the union recursively.
1999
2000        Example:
2001            >>> from sqlglot import parse_one
2002            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2003            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2004
2005        Args:
2006            *expressions: the SQL code strings to parse.
2007                If an `Expression` instance is passed, it will be used as-is.
2008            append: if `True`, add to any existing expressions.
2009                Otherwise, this resets the expressions.
2010            dialect: the dialect used to parse the input expressions.
2011            copy: if `False`, modify this expression instance in-place.
2012            opts: other options to use to parse the input expressions.
2013
2014        Returns:
2015            Union: the modified expression.
2016        """
2017        this = self.copy() if copy else self
2018        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2019        this.expression.unnest().select(
2020            *expressions, append=append, dialect=dialect, copy=False, **opts
2021        )
2022        return this
2023
2024    @property
2025    def named_selects(self):
2026        return self.this.unnest().named_selects
2027
2028    @property
2029    def is_star(self) -> bool:
2030        return self.this.is_star or self.expression.is_star
2031
2032    @property
2033    def selects(self):
2034        return self.this.unnest().selects
2035
2036    @property
2037    def left(self):
2038        return self.this
2039
2040    @property
2041    def right(self):
2042        return self.expression
2043
2044
2045class Except(Union):
2046    pass
2047
2048
2049class Intersect(Union):
2050    pass
2051
2052
2053class Unnest(UDTF):
2054    arg_types = {
2055        "expressions": True,
2056        "ordinality": False,
2057        "alias": False,
2058        "offset": False,
2059    }
2060
2061
2062class Update(Expression):
2063    arg_types = {
2064        "with": False,
2065        "this": False,
2066        "expressions": True,
2067        "from": False,
2068        "where": False,
2069        "returning": False,
2070    }
2071
2072
2073class Values(UDTF):
2074    arg_types = {
2075        "expressions": True,
2076        "ordinality": False,
2077        "alias": False,
2078    }
2079
2080
2081class Var(Expression):
2082    pass
2083
2084
2085class Schema(Expression):
2086    arg_types = {"this": False, "expressions": False}
2087
2088
2089# Used to represent the FOR UPDATE and FOR SHARE locking read types.
2090# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
2091class Lock(Expression):
2092    arg_types = {"update": True}
2093
2094
2095class Select(Subqueryable):
2096    arg_types = {
2097        "with": False,
2098        "kind": False,
2099        "expressions": False,
2100        "hint": False,
2101        "distinct": False,
2102        "into": False,
2103        "from": False,
2104        **QUERY_MODIFIERS,
2105    }
2106
2107    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2108        """
2109        Set the FROM expression.
2110
2111        Example:
2112            >>> Select().from_("tbl").select("x").sql()
2113            'SELECT x FROM tbl'
2114
2115        Args:
2116            *expressions (str | Expression): the SQL code strings to parse.
2117                If a `From` instance is passed, this is used as-is.
2118                If another `Expression` instance is passed, it will be wrapped in a `From`.
2119            append (bool): if `True`, add to any existing expressions.
2120                Otherwise, this flattens all the `From` expression into a single expression.
2121            dialect (str): the dialect used to parse the input expression.
2122            copy (bool): if `False`, modify this expression instance in-place.
2123            opts (kwargs): other options to use to parse the input expressions.
2124
2125        Returns:
2126            Select: the modified expression.
2127        """
2128        return _apply_child_list_builder(
2129            *expressions,
2130            instance=self,
2131            arg="from",
2132            append=append,
2133            copy=copy,
2134            prefix="FROM",
2135            into=From,
2136            dialect=dialect,
2137            **opts,
2138        )
2139
2140    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2141        """
2142        Set the GROUP BY expression.
2143
2144        Example:
2145            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2146            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2147
2148        Args:
2149            *expressions (str | Expression): the SQL code strings to parse.
2150                If a `Group` instance is passed, this is used as-is.
2151                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2152                If nothing is passed in then a group by is not applied to the expression
2153            append (bool): if `True`, add to any existing expressions.
2154                Otherwise, this flattens all the `Group` expression into a single expression.
2155            dialect (str): the dialect used to parse the input expression.
2156            copy (bool): if `False`, modify this expression instance in-place.
2157            opts (kwargs): other options to use to parse the input expressions.
2158
2159        Returns:
2160            Select: the modified expression.
2161        """
2162        if not expressions:
2163            return self if not copy else self.copy()
2164        return _apply_child_list_builder(
2165            *expressions,
2166            instance=self,
2167            arg="group",
2168            append=append,
2169            copy=copy,
2170            prefix="GROUP BY",
2171            into=Group,
2172            dialect=dialect,
2173            **opts,
2174        )
2175
2176    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2177        """
2178        Set the ORDER BY expression.
2179
2180        Example:
2181            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2182            'SELECT x FROM tbl ORDER BY x DESC'
2183
2184        Args:
2185            *expressions (str | Expression): the SQL code strings to parse.
2186                If a `Group` instance is passed, this is used as-is.
2187                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2188            append (bool): if `True`, add to any existing expressions.
2189                Otherwise, this flattens all the `Order` expression into a single expression.
2190            dialect (str): the dialect used to parse the input expression.
2191            copy (bool): if `False`, modify this expression instance in-place.
2192            opts (kwargs): other options to use to parse the input expressions.
2193
2194        Returns:
2195            Select: the modified expression.
2196        """
2197        return _apply_child_list_builder(
2198            *expressions,
2199            instance=self,
2200            arg="order",
2201            append=append,
2202            copy=copy,
2203            prefix="ORDER BY",
2204            into=Order,
2205            dialect=dialect,
2206            **opts,
2207        )
2208
2209    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2210        """
2211        Set the SORT BY expression.
2212
2213        Example:
2214            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2215            'SELECT x FROM tbl SORT BY x DESC'
2216
2217        Args:
2218            *expressions (str | Expression): the SQL code strings to parse.
2219                If a `Group` instance is passed, this is used as-is.
2220                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2221            append (bool): if `True`, add to any existing expressions.
2222                Otherwise, this flattens all the `Order` expression into a single expression.
2223            dialect (str): the dialect used to parse the input expression.
2224            copy (bool): if `False`, modify this expression instance in-place.
2225            opts (kwargs): other options to use to parse the input expressions.
2226
2227        Returns:
2228            Select: the modified expression.
2229        """
2230        return _apply_child_list_builder(
2231            *expressions,
2232            instance=self,
2233            arg="sort",
2234            append=append,
2235            copy=copy,
2236            prefix="SORT BY",
2237            into=Sort,
2238            dialect=dialect,
2239            **opts,
2240        )
2241
2242    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2243        """
2244        Set the CLUSTER BY expression.
2245
2246        Example:
2247            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2248            'SELECT x FROM tbl CLUSTER BY x DESC'
2249
2250        Args:
2251            *expressions (str | Expression): the SQL code strings to parse.
2252                If a `Group` instance is passed, this is used as-is.
2253                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2254            append (bool): if `True`, add to any existing expressions.
2255                Otherwise, this flattens all the `Order` expression into a single expression.
2256            dialect (str): the dialect used to parse the input expression.
2257            copy (bool): if `False`, modify this expression instance in-place.
2258            opts (kwargs): other options to use to parse the input expressions.
2259
2260        Returns:
2261            Select: the modified expression.
2262        """
2263        return _apply_child_list_builder(
2264            *expressions,
2265            instance=self,
2266            arg="cluster",
2267            append=append,
2268            copy=copy,
2269            prefix="CLUSTER BY",
2270            into=Cluster,
2271            dialect=dialect,
2272            **opts,
2273        )
2274
2275    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2276        """
2277        Set the LIMIT expression.
2278
2279        Example:
2280            >>> Select().from_("tbl").select("x").limit(10).sql()
2281            'SELECT x FROM tbl LIMIT 10'
2282
2283        Args:
2284            expression (str | int | Expression): the SQL code string to parse.
2285                This can also be an integer.
2286                If a `Limit` instance is passed, this is used as-is.
2287                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2288            dialect (str): the dialect used to parse the input expression.
2289            copy (bool): if `False`, modify this expression instance in-place.
2290            opts (kwargs): other options to use to parse the input expressions.
2291
2292        Returns:
2293            Select: the modified expression.
2294        """
2295        return _apply_builder(
2296            expression=expression,
2297            instance=self,
2298            arg="limit",
2299            into=Limit,
2300            prefix="LIMIT",
2301            dialect=dialect,
2302            copy=copy,
2303            **opts,
2304        )
2305
2306    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2307        """
2308        Set the OFFSET expression.
2309
2310        Example:
2311            >>> Select().from_("tbl").select("x").offset(10).sql()
2312            'SELECT x FROM tbl OFFSET 10'
2313
2314        Args:
2315            expression (str | int | Expression): the SQL code string to parse.
2316                This can also be an integer.
2317                If a `Offset` instance is passed, this is used as-is.
2318                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2319            dialect (str): the dialect used to parse the input expression.
2320            copy (bool): if `False`, modify this expression instance in-place.
2321            opts (kwargs): other options to use to parse the input expressions.
2322
2323        Returns:
2324            Select: the modified expression.
2325        """
2326        return _apply_builder(
2327            expression=expression,
2328            instance=self,
2329            arg="offset",
2330            into=Offset,
2331            prefix="OFFSET",
2332            dialect=dialect,
2333            copy=copy,
2334            **opts,
2335        )
2336
2337    def select(
2338        self,
2339        *expressions: ExpOrStr,
2340        append: bool = True,
2341        dialect: DialectType = None,
2342        copy: bool = True,
2343        **opts,
2344    ) -> Select:
2345        """
2346        Append to or set the SELECT expressions.
2347
2348        Example:
2349            >>> Select().select("x", "y").sql()
2350            'SELECT x, y'
2351
2352        Args:
2353            *expressions: the SQL code strings to parse.
2354                If an `Expression` instance is passed, it will be used as-is.
2355            append: if `True`, add to any existing expressions.
2356                Otherwise, this resets the expressions.
2357            dialect: the dialect used to parse the input expressions.
2358            copy: if `False`, modify this expression instance in-place.
2359            opts: other options to use to parse the input expressions.
2360
2361        Returns:
2362            Select: the modified expression.
2363        """
2364        return _apply_list_builder(
2365            *expressions,
2366            instance=self,
2367            arg="expressions",
2368            append=append,
2369            dialect=dialect,
2370            copy=copy,
2371            **opts,
2372        )
2373
2374    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2375        """
2376        Append to or set the LATERAL expressions.
2377
2378        Example:
2379            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2380            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2381
2382        Args:
2383            *expressions (str | Expression): the SQL code strings to parse.
2384                If an `Expression` instance is passed, it will be used as-is.
2385            append (bool): if `True`, add to any existing expressions.
2386                Otherwise, this resets the expressions.
2387            dialect (str): the dialect used to parse the input expressions.
2388            copy (bool): if `False`, modify this expression instance in-place.
2389            opts (kwargs): other options to use to parse the input expressions.
2390
2391        Returns:
2392            Select: the modified expression.
2393        """
2394        return _apply_list_builder(
2395            *expressions,
2396            instance=self,
2397            arg="laterals",
2398            append=append,
2399            into=Lateral,
2400            prefix="LATERAL VIEW",
2401            dialect=dialect,
2402            copy=copy,
2403            **opts,
2404        )
2405
2406    def join(
2407        self,
2408        expression,
2409        on=None,
2410        using=None,
2411        append=True,
2412        join_type=None,
2413        join_alias=None,
2414        dialect=None,
2415        copy=True,
2416        **opts,
2417    ) -> Select:
2418        """
2419        Append to or set the JOIN expressions.
2420
2421        Example:
2422            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2423            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2424
2425            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2426            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2427
2428            Use `join_type` to change the type of join:
2429
2430            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2431            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2432
2433        Args:
2434            expression (str | Expression): the SQL code string to parse.
2435                If an `Expression` instance is passed, it will be used as-is.
2436            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2437                If an `Expression` instance is passed, it will be used as-is.
2438            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2439                If an `Expression` instance is passed, it will be used as-is.
2440            append (bool): if `True`, add to any existing expressions.
2441                Otherwise, this resets the expressions.
2442            join_type (str): If set, alter the parsed join type
2443            dialect (str): the dialect used to parse the input expressions.
2444            copy (bool): if `False`, modify this expression instance in-place.
2445            opts (kwargs): other options to use to parse the input expressions.
2446
2447        Returns:
2448            Select: the modified expression.
2449        """
2450        parse_args = {"dialect": dialect, **opts}
2451
2452        try:
2453            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2454        except ParseError:
2455            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2456
2457        join = expression if isinstance(expression, Join) else Join(this=expression)
2458
2459        if isinstance(join.this, Select):
2460            join.this.replace(join.this.subquery())
2461
2462        if join_type:
2463            natural: t.Optional[Token]
2464            side: t.Optional[Token]
2465            kind: t.Optional[Token]
2466
2467            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2468
2469            if natural:
2470                join.set("natural", True)
2471            if side:
2472                join.set("side", side.text)
2473            if kind:
2474                join.set("kind", kind.text)
2475
2476        if on:
2477            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2478            join.set("on", on)
2479
2480        if using:
2481            join = _apply_list_builder(
2482                *ensure_collection(using),
2483                instance=join,
2484                arg="using",
2485                append=append,
2486                copy=copy,
2487                **opts,
2488            )
2489
2490        if join_alias:
2491            join.set("this", alias_(join.this, join_alias, table=True))
2492        return _apply_list_builder(
2493            join,
2494            instance=self,
2495            arg="joins",
2496            append=append,
2497            copy=copy,
2498            **opts,
2499        )
2500
2501    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2502        """
2503        Append to or set the WHERE expressions.
2504
2505        Example:
2506            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2507            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2508
2509        Args:
2510            *expressions (str | Expression): the SQL code strings to parse.
2511                If an `Expression` instance is passed, it will be used as-is.
2512                Multiple expressions are combined with an AND operator.
2513            append (bool): if `True`, AND the new expressions to any existing expression.
2514                Otherwise, this resets the expression.
2515            dialect (str): the dialect used to parse the input expressions.
2516            copy (bool): if `False`, modify this expression instance in-place.
2517            opts (kwargs): other options to use to parse the input expressions.
2518
2519        Returns:
2520            Select: the modified expression.
2521        """
2522        return _apply_conjunction_builder(
2523            *expressions,
2524            instance=self,
2525            arg="where",
2526            append=append,
2527            into=Where,
2528            dialect=dialect,
2529            copy=copy,
2530            **opts,
2531        )
2532
2533    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2534        """
2535        Append to or set the HAVING expressions.
2536
2537        Example:
2538            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2539            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2540
2541        Args:
2542            *expressions (str | Expression): the SQL code strings to parse.
2543                If an `Expression` instance is passed, it will be used as-is.
2544                Multiple expressions are combined with an AND operator.
2545            append (bool): if `True`, AND the new expressions to any existing expression.
2546                Otherwise, this resets the expression.
2547            dialect (str): the dialect used to parse the input expressions.
2548            copy (bool): if `False`, modify this expression instance in-place.
2549            opts (kwargs): other options to use to parse the input expressions.
2550
2551        Returns:
2552            Select: the modified expression.
2553        """
2554        return _apply_conjunction_builder(
2555            *expressions,
2556            instance=self,
2557            arg="having",
2558            append=append,
2559            into=Having,
2560            dialect=dialect,
2561            copy=copy,
2562            **opts,
2563        )
2564
2565    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2566        return _apply_list_builder(
2567            *expressions,
2568            instance=self,
2569            arg="windows",
2570            append=append,
2571            into=Window,
2572            dialect=dialect,
2573            copy=copy,
2574            **opts,
2575        )
2576
2577    def qualify(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2578        return _apply_conjunction_builder(
2579            *expressions,
2580            instance=self,
2581            arg="qualify",
2582            append=append,
2583            into=Qualify,
2584            dialect=dialect,
2585            copy=copy,
2586            **opts,
2587        )
2588
2589    def distinct(self, distinct=True, copy=True) -> Select:
2590        """
2591        Set the OFFSET expression.
2592
2593        Example:
2594            >>> Select().from_("tbl").select("x").distinct().sql()
2595            'SELECT DISTINCT x FROM tbl'
2596
2597        Args:
2598            distinct (bool): whether the Select should be distinct
2599            copy (bool): if `False`, modify this expression instance in-place.
2600
2601        Returns:
2602            Select: the modified expression.
2603        """
2604        instance = _maybe_copy(self, copy)
2605        instance.set("distinct", Distinct() if distinct else None)
2606        return instance
2607
2608    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2609        """
2610        Convert this expression to a CREATE TABLE AS statement.
2611
2612        Example:
2613            >>> Select().select("*").from_("tbl").ctas("x").sql()
2614            'CREATE TABLE x AS SELECT * FROM tbl'
2615
2616        Args:
2617            table (str | Expression): the SQL code string to parse as the table name.
2618                If another `Expression` instance is passed, it will be used as-is.
2619            properties (dict): an optional mapping of table properties
2620            dialect (str): the dialect used to parse the input table.
2621            copy (bool): if `False`, modify this expression instance in-place.
2622            opts (kwargs): other options to use to parse the input table.
2623
2624        Returns:
2625            Create: the CREATE TABLE AS expression
2626        """
2627        instance = _maybe_copy(self, copy)
2628        table_expression = maybe_parse(
2629            table,
2630            into=Table,
2631            dialect=dialect,
2632            **opts,
2633        )
2634        properties_expression = None
2635        if properties:
2636            properties_expression = Properties.from_dict(properties)
2637
2638        return Create(
2639            this=table_expression,
2640            kind="table",
2641            expression=instance,
2642            properties=properties_expression,
2643        )
2644
2645    def lock(self, update: bool = True, copy: bool = True) -> Select:
2646        """
2647        Set the locking read mode for this expression.
2648
2649        Examples:
2650            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2651            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2652
2653            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2654            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2655
2656        Args:
2657            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2658            copy: if `False`, modify this expression instance in-place.
2659
2660        Returns:
2661            The modified expression.
2662        """
2663
2664        inst = _maybe_copy(self, copy)
2665        inst.set("lock", Lock(update=update))
2666
2667        return inst
2668
2669    @property
2670    def named_selects(self) -> t.List[str]:
2671        return [e.output_name for e in self.expressions if e.alias_or_name]
2672
2673    @property
2674    def is_star(self) -> bool:
2675        return any(expression.is_star for expression in self.expressions)
2676
2677    @property
2678    def selects(self) -> t.List[Expression]:
2679        return self.expressions
2680
2681
2682class Subquery(DerivedTable, Unionable):
2683    arg_types = {
2684        "this": True,
2685        "alias": False,
2686        "with": False,
2687        **QUERY_MODIFIERS,
2688    }
2689
2690    def unnest(self):
2691        """
2692        Returns the first non subquery.
2693        """
2694        expression = self
2695        while isinstance(expression, Subquery):
2696            expression = expression.this
2697        return expression
2698
2699    @property
2700    def is_star(self) -> bool:
2701        return self.this.is_star
2702
2703    @property
2704    def output_name(self):
2705        return self.alias
2706
2707
2708class TableSample(Expression):
2709    arg_types = {
2710        "this": False,
2711        "method": False,
2712        "bucket_numerator": False,
2713        "bucket_denominator": False,
2714        "bucket_field": False,
2715        "percent": False,
2716        "rows": False,
2717        "size": False,
2718        "seed": False,
2719        "kind": False,
2720    }
2721
2722
2723class Tag(Expression):
2724    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2725
2726    arg_types = {
2727        "this": False,
2728        "prefix": False,
2729        "postfix": False,
2730    }
2731
2732
2733class Pivot(Expression):
2734    arg_types = {
2735        "this": False,
2736        "alias": False,
2737        "expressions": True,
2738        "field": True,
2739        "unpivot": True,
2740    }
2741
2742
2743class Window(Expression):
2744    arg_types = {
2745        "this": True,
2746        "partition_by": False,
2747        "order": False,
2748        "spec": False,
2749        "alias": False,
2750    }
2751
2752
2753class WindowSpec(Expression):
2754    arg_types = {
2755        "kind": False,
2756        "start": False,
2757        "start_side": False,
2758        "end": False,
2759        "end_side": False,
2760    }
2761
2762
2763class Where(Expression):
2764    pass
2765
2766
2767class Star(Expression):
2768    arg_types = {"except": False, "replace": False}
2769
2770    @property
2771    def name(self) -> str:
2772        return "*"
2773
2774    @property
2775    def output_name(self):
2776        return self.name
2777
2778
2779class Parameter(Expression):
2780    arg_types = {"this": True, "wrapped": False}
2781
2782
2783class SessionParameter(Expression):
2784    arg_types = {"this": True, "kind": False}
2785
2786
2787class Placeholder(Expression):
2788    arg_types = {"this": False}
2789
2790
2791class Null(Condition):
2792    arg_types: t.Dict[str, t.Any] = {}
2793
2794    @property
2795    def name(self) -> str:
2796        return "NULL"
2797
2798
2799class Boolean(Condition):
2800    pass
2801
2802
2803class DataType(Expression):
2804    arg_types = {
2805        "this": True,
2806        "expressions": False,
2807        "nested": False,
2808        "values": False,
2809        "prefix": False,
2810    }
2811
2812    class Type(AutoName):
2813        CHAR = auto()
2814        NCHAR = auto()
2815        VARCHAR = auto()
2816        NVARCHAR = auto()
2817        TEXT = auto()
2818        MEDIUMTEXT = auto()
2819        LONGTEXT = auto()
2820        MEDIUMBLOB = auto()
2821        LONGBLOB = auto()
2822        BINARY = auto()
2823        VARBINARY = auto()
2824        INT = auto()
2825        UINT = auto()
2826        TINYINT = auto()
2827        UTINYINT = auto()
2828        SMALLINT = auto()
2829        USMALLINT = auto()
2830        BIGINT = auto()
2831        UBIGINT = auto()
2832        FLOAT = auto()
2833        DOUBLE = auto()
2834        DECIMAL = auto()
2835        BIGDECIMAL = auto()
2836        BIT = auto()
2837        BOOLEAN = auto()
2838        JSON = auto()
2839        JSONB = auto()
2840        INTERVAL = auto()
2841        TIME = auto()
2842        TIMESTAMP = auto()
2843        TIMESTAMPTZ = auto()
2844        TIMESTAMPLTZ = auto()
2845        DATE = auto()
2846        DATETIME = auto()
2847        ARRAY = auto()
2848        MAP = auto()
2849        UUID = auto()
2850        GEOGRAPHY = auto()
2851        GEOMETRY = auto()
2852        STRUCT = auto()
2853        NULLABLE = auto()
2854        HLLSKETCH = auto()
2855        HSTORE = auto()
2856        SUPER = auto()
2857        SERIAL = auto()
2858        SMALLSERIAL = auto()
2859        BIGSERIAL = auto()
2860        XML = auto()
2861        UNIQUEIDENTIFIER = auto()
2862        MONEY = auto()
2863        SMALLMONEY = auto()
2864        ROWVERSION = auto()
2865        IMAGE = auto()
2866        VARIANT = auto()
2867        OBJECT = auto()
2868        INET = auto()
2869        NULL = auto()
2870        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2871
2872    TEXT_TYPES = {
2873        Type.CHAR,
2874        Type.NCHAR,
2875        Type.VARCHAR,
2876        Type.NVARCHAR,
2877        Type.TEXT,
2878    }
2879
2880    INTEGER_TYPES = {
2881        Type.INT,
2882        Type.TINYINT,
2883        Type.SMALLINT,
2884        Type.BIGINT,
2885    }
2886
2887    FLOAT_TYPES = {
2888        Type.FLOAT,
2889        Type.DOUBLE,
2890    }
2891
2892    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2893
2894    TEMPORAL_TYPES = {
2895        Type.TIMESTAMP,
2896        Type.TIMESTAMPTZ,
2897        Type.TIMESTAMPLTZ,
2898        Type.DATE,
2899        Type.DATETIME,
2900    }
2901
2902    @classmethod
2903    def build(
2904        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2905    ) -> DataType:
2906        from sqlglot import parse_one
2907
2908        if isinstance(dtype, str):
2909            if dtype.upper() in cls.Type.__members__:
2910                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2911            else:
2912                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2913            if data_type_exp is None:
2914                raise ValueError(f"Unparsable data type value: {dtype}")
2915        elif isinstance(dtype, DataType.Type):
2916            data_type_exp = DataType(this=dtype)
2917        elif isinstance(dtype, DataType):
2918            return dtype
2919        else:
2920            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2921        return DataType(**{**data_type_exp.args, **kwargs})
2922
2923    def is_type(self, dtype: DataType.Type) -> bool:
2924        return self.this == dtype
2925
2926
2927# https://www.postgresql.org/docs/15/datatype-pseudo.html
2928class PseudoType(Expression):
2929    pass
2930
2931
2932class StructKwarg(Expression):
2933    arg_types = {"this": True, "expression": True}
2934
2935
2936# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2937class SubqueryPredicate(Predicate):
2938    pass
2939
2940
2941class All(SubqueryPredicate):
2942    pass
2943
2944
2945class Any(SubqueryPredicate):
2946    pass
2947
2948
2949class Exists(SubqueryPredicate):
2950    pass
2951
2952
2953# Commands to interact with the databases or engines. For most of the command
2954# expressions we parse whatever comes after the command's name as a string.
2955class Command(Expression):
2956    arg_types = {"this": True, "expression": False}
2957
2958
2959class Transaction(Expression):
2960    arg_types = {"this": False, "modes": False}
2961
2962
2963class Commit(Expression):
2964    arg_types = {"chain": False}
2965
2966
2967class Rollback(Expression):
2968    arg_types = {"savepoint": False}
2969
2970
2971class AlterTable(Expression):
2972    arg_types = {"this": True, "actions": True, "exists": False}
2973
2974
2975class AddConstraint(Expression):
2976    arg_types = {"this": False, "expression": False, "enforced": False}
2977
2978
2979class DropPartition(Expression):
2980    arg_types = {"expressions": True, "exists": False}
2981
2982
2983# Binary expressions like (ADD a b)
2984class Binary(Expression):
2985    arg_types = {"this": True, "expression": True}
2986
2987    @property
2988    def left(self):
2989        return self.this
2990
2991    @property
2992    def right(self):
2993        return self.expression
2994
2995
2996class Add(Binary):
2997    pass
2998
2999
3000class Connector(Binary, Condition):
3001    pass
3002
3003
3004class And(Connector):
3005    pass
3006
3007
3008class Or(Connector):
3009    pass
3010
3011
3012class BitwiseAnd(Binary):
3013    pass
3014
3015
3016class BitwiseLeftShift(Binary):
3017    pass
3018
3019
3020class BitwiseOr(Binary):
3021    pass
3022
3023
3024class BitwiseRightShift(Binary):
3025    pass
3026
3027
3028class BitwiseXor(Binary):
3029    pass
3030
3031
3032class Div(Binary):
3033    pass
3034
3035
3036class Overlaps(Binary):
3037    pass
3038
3039
3040class Dot(Binary):
3041    @property
3042    def name(self) -> str:
3043        return self.expression.name
3044
3045    @classmethod
3046    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3047        """Build a Dot object with a sequence of expressions."""
3048        if len(expressions) < 2:
3049            raise ValueError(f"Dot requires >= 2 expressions.")
3050
3051        a, b, *expressions = expressions
3052        dot = Dot(this=a, expression=b)
3053
3054        for expression in expressions:
3055            dot = Dot(this=dot, expression=expression)
3056
3057        return dot
3058
3059
3060class DPipe(Binary):
3061    pass
3062
3063
3064class EQ(Binary, Predicate):
3065    pass
3066
3067
3068class NullSafeEQ(Binary, Predicate):
3069    pass
3070
3071
3072class NullSafeNEQ(Binary, Predicate):
3073    pass
3074
3075
3076class Distance(Binary):
3077    pass
3078
3079
3080class Escape(Binary):
3081    pass
3082
3083
3084class Glob(Binary, Predicate):
3085    pass
3086
3087
3088class GT(Binary, Predicate):
3089    pass
3090
3091
3092class GTE(Binary, Predicate):
3093    pass
3094
3095
3096class ILike(Binary, Predicate):
3097    pass
3098
3099
3100class ILikeAny(Binary, Predicate):
3101    pass
3102
3103
3104class IntDiv(Binary):
3105    pass
3106
3107
3108class Is(Binary, Predicate):
3109    pass
3110
3111
3112class Kwarg(Binary):
3113    """Kwarg in special functions like func(kwarg => y)."""
3114
3115
3116class Like(Binary, Predicate):
3117    pass
3118
3119
3120class LikeAny(Binary, Predicate):
3121    pass
3122
3123
3124class LT(Binary, Predicate):
3125    pass
3126
3127
3128class LTE(Binary, Predicate):
3129    pass
3130
3131
3132class Mod(Binary):
3133    pass
3134
3135
3136class Mul(Binary):
3137    pass
3138
3139
3140class NEQ(Binary, Predicate):
3141    pass
3142
3143
3144class SimilarTo(Binary, Predicate):
3145    pass
3146
3147
3148class Slice(Binary):
3149    arg_types = {"this": False, "expression": False}
3150
3151
3152class Sub(Binary):
3153    pass
3154
3155
3156class ArrayOverlaps(Binary):
3157    pass
3158
3159
3160# Unary Expressions
3161# (NOT a)
3162class Unary(Expression):
3163    pass
3164
3165
3166class BitwiseNot(Unary):
3167    pass
3168
3169
3170class Not(Unary, Condition):
3171    pass
3172
3173
3174class Paren(Unary, Condition):
3175    arg_types = {"this": True, "with": False}
3176
3177
3178class Neg(Unary):
3179    pass
3180
3181
3182class Alias(Expression):
3183    arg_types = {"this": True, "alias": False}
3184
3185    @property
3186    def output_name(self):
3187        return self.alias
3188
3189
3190class Aliases(Expression):
3191    arg_types = {"this": True, "expressions": True}
3192
3193    @property
3194    def aliases(self):
3195        return self.expressions
3196
3197
3198class AtTimeZone(Expression):
3199    arg_types = {"this": True, "zone": True}
3200
3201
3202class Between(Predicate):
3203    arg_types = {"this": True, "low": True, "high": True}
3204
3205
3206class Bracket(Condition):
3207    arg_types = {"this": True, "expressions": True}
3208
3209
3210class Distinct(Expression):
3211    arg_types = {"expressions": False, "on": False}
3212
3213
3214class In(Predicate):
3215    arg_types = {
3216        "this": True,
3217        "expressions": False,
3218        "query": False,
3219        "unnest": False,
3220        "field": False,
3221        "is_global": False,
3222    }
3223
3224
3225class TimeUnit(Expression):
3226    """Automatically converts unit arg into a var."""
3227
3228    arg_types = {"unit": False}
3229
3230    def __init__(self, **args):
3231        unit = args.get("unit")
3232        if isinstance(unit, (Column, Literal)):
3233            args["unit"] = Var(this=unit.name)
3234        elif isinstance(unit, Week):
3235            unit.set("this", Var(this=unit.this.name))
3236        super().__init__(**args)
3237
3238
3239class Interval(TimeUnit):
3240    arg_types = {"this": False, "unit": False}
3241
3242
3243class IgnoreNulls(Expression):
3244    pass
3245
3246
3247class RespectNulls(Expression):
3248    pass
3249
3250
3251# Functions
3252class Func(Condition):
3253    """
3254    The base class for all function expressions.
3255
3256    Attributes:
3257        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3258            treated as a variable length argument and the argument's value will be stored as a list.
3259        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3260            for this function expression. These values are used to map this node to a name during parsing
3261            as well as to provide the function's name during SQL string generation. By default the SQL
3262            name is set to the expression's class name transformed to snake case.
3263    """
3264
3265    is_var_len_args = False
3266
3267    @classmethod
3268    def from_arg_list(cls, args):
3269        if cls.is_var_len_args:
3270            all_arg_keys = list(cls.arg_types)
3271            # If this function supports variable length argument treat the last argument as such.
3272            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3273            num_non_var = len(non_var_len_arg_keys)
3274
3275            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3276            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3277        else:
3278            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3279
3280        return cls(**args_dict)
3281
3282    @classmethod
3283    def sql_names(cls):
3284        if cls is Func:
3285            raise NotImplementedError(
3286                "SQL name is only supported by concrete function implementations"
3287            )
3288        if "_sql_names" not in cls.__dict__:
3289            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3290        return cls._sql_names
3291
3292    @classmethod
3293    def sql_name(cls):
3294        return cls.sql_names()[0]
3295
3296    @classmethod
3297    def default_parser_mappings(cls):
3298        return {name: cls.from_arg_list for name in cls.sql_names()}
3299
3300
3301class AggFunc(Func):
3302    pass
3303
3304
3305class Abs(Func):
3306    pass
3307
3308
3309class Anonymous(Func):
3310    arg_types = {"this": True, "expressions": False}
3311    is_var_len_args = True
3312
3313
3314# https://docs.snowflake.com/en/sql-reference/functions/hll
3315# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
3316class Hll(AggFunc):
3317    arg_types = {"this": True, "expressions": False}
3318    is_var_len_args = True
3319
3320
3321class ApproxDistinct(AggFunc):
3322    arg_types = {"this": True, "accuracy": False}
3323
3324
3325class Array(Func):
3326    arg_types = {"expressions": False}
3327    is_var_len_args = True
3328
3329
3330# https://docs.snowflake.com/en/sql-reference/functions/to_char
3331class ToChar(Func):
3332    arg_types = {"this": True, "format": False}
3333
3334
3335class GenerateSeries(Func):
3336    arg_types = {"start": True, "end": True, "step": False}
3337
3338
3339class ArrayAgg(AggFunc):
3340    pass
3341
3342
3343class ArrayAll(Func):
3344    arg_types = {"this": True, "expression": True}
3345
3346
3347class ArrayAny(Func):
3348    arg_types = {"this": True, "expression": True}
3349
3350
3351class ArrayConcat(Func):
3352    arg_types = {"this": True, "expressions": False}
3353    is_var_len_args = True
3354
3355
3356class ArrayContains(Binary, Func):
3357    pass
3358
3359
3360class ArrayContained(Binary):
3361    pass
3362
3363
3364class ArrayFilter(Func):
3365    arg_types = {"this": True, "expression": True}
3366    _sql_names = ["FILTER", "ARRAY_FILTER"]
3367
3368
3369class ArrayJoin(Func):
3370    arg_types = {"this": True, "expression": True, "null": False}
3371
3372
3373class ArraySize(Func):
3374    arg_types = {"this": True, "expression": False}
3375
3376
3377class ArraySort(Func):
3378    arg_types = {"this": True, "expression": False}
3379
3380
3381class ArraySum(Func):
3382    pass
3383
3384
3385class ArrayUnionAgg(AggFunc):
3386    pass
3387
3388
3389class Avg(AggFunc):
3390    pass
3391
3392
3393class AnyValue(AggFunc):
3394    pass
3395
3396
3397class Case(Func):
3398    arg_types = {"this": False, "ifs": True, "default": False}
3399
3400
3401class Cast(Func):
3402    arg_types = {"this": True, "to": True}
3403
3404    @property
3405    def name(self) -> str:
3406        return self.this.name
3407
3408    @property
3409    def to(self):
3410        return self.args["to"]
3411
3412    @property
3413    def output_name(self):
3414        return self.name
3415
3416    def is_type(self, dtype: DataType.Type) -> bool:
3417        return self.to.is_type(dtype)
3418
3419
3420class Collate(Binary):
3421    pass
3422
3423
3424class TryCast(Cast):
3425    pass
3426
3427
3428class Ceil(Func):
3429    arg_types = {"this": True, "decimals": False}
3430    _sql_names = ["CEIL", "CEILING"]
3431
3432
3433class Coalesce(Func):
3434    arg_types = {"this": True, "expressions": False}
3435    is_var_len_args = True
3436
3437
3438class Concat(Func):
3439    arg_types = {"expressions": True}
3440    is_var_len_args = True
3441
3442
3443class ConcatWs(Concat):
3444    _sql_names = ["CONCAT_WS"]
3445
3446
3447class Count(AggFunc):
3448    arg_types = {"this": False}
3449
3450
3451class CountIf(AggFunc):
3452    pass
3453
3454
3455class CurrentDate(Func):
3456    arg_types = {"this": False}
3457
3458
3459class CurrentDatetime(Func):
3460    arg_types = {"this": False}
3461
3462
3463class CurrentTime(Func):
3464    arg_types = {"this": False}
3465
3466
3467class CurrentTimestamp(Func):
3468    arg_types = {"this": False}
3469
3470
3471class CurrentUser(Func):
3472    arg_types = {"this": False}
3473
3474
3475class DateAdd(Func, TimeUnit):
3476    arg_types = {"this": True, "expression": True, "unit": False}
3477
3478
3479class DateSub(Func, TimeUnit):
3480    arg_types = {"this": True, "expression": True, "unit": False}
3481
3482
3483class DateDiff(Func, TimeUnit):
3484    _sql_names = ["DATEDIFF", "DATE_DIFF"]
3485    arg_types = {"this": True, "expression": True, "unit": False}
3486
3487
3488class DateTrunc(Func):
3489    arg_types = {"unit": True, "this": True, "zone": False}
3490
3491
3492class DatetimeAdd(Func, TimeUnit):
3493    arg_types = {"this": True, "expression": True, "unit": False}
3494
3495
3496class DatetimeSub(Func, TimeUnit):
3497    arg_types = {"this": True, "expression": True, "unit": False}
3498
3499
3500class DatetimeDiff(Func, TimeUnit):
3501    arg_types = {"this": True, "expression": True, "unit": False}
3502
3503
3504class DatetimeTrunc(Func, TimeUnit):
3505    arg_types = {"this": True, "unit": True, "zone": False}
3506
3507
3508class DayOfWeek(Func):
3509    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3510
3511
3512class DayOfMonth(Func):
3513    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3514
3515
3516class DayOfYear(Func):
3517    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3518
3519
3520class WeekOfYear(Func):
3521    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3522
3523
3524class LastDateOfMonth(Func):
3525    pass
3526
3527
3528class Extract(Func):
3529    arg_types = {"this": True, "expression": True}
3530
3531
3532class TimestampAdd(Func, TimeUnit):
3533    arg_types = {"this": True, "expression": True, "unit": False}
3534
3535
3536class TimestampSub(Func, TimeUnit):
3537    arg_types = {"this": True, "expression": True, "unit": False}
3538
3539
3540class TimestampDiff(Func, TimeUnit):
3541    arg_types = {"this": True, "expression": True, "unit": False}
3542
3543
3544class TimestampTrunc(Func, TimeUnit):
3545    arg_types = {"this": True, "unit": True, "zone": False}
3546
3547
3548class TimeAdd(Func, TimeUnit):
3549    arg_types = {"this": True, "expression": True, "unit": False}
3550
3551
3552class TimeSub(Func, TimeUnit):
3553    arg_types = {"this": True, "expression": True, "unit": False}
3554
3555
3556class TimeDiff(Func, TimeUnit):
3557    arg_types = {"this": True, "expression": True, "unit": False}
3558
3559
3560class TimeTrunc(Func, TimeUnit):
3561    arg_types = {"this": True, "unit": True, "zone": False}
3562
3563
3564class DateFromParts(Func):
3565    _sql_names = ["DATEFROMPARTS"]
3566    arg_types = {"year": True, "month": True, "day": True}
3567
3568
3569class DateStrToDate(Func):
3570    pass
3571
3572
3573class DateToDateStr(Func):
3574    pass
3575
3576
3577class DateToDi(Func):
3578    pass
3579
3580
3581class Day(Func):
3582    pass
3583
3584
3585class Decode(Func):
3586    arg_types = {"this": True, "charset": True, "replace": False}
3587
3588
3589class DiToDate(Func):
3590    pass
3591
3592
3593class Encode(Func):
3594    arg_types = {"this": True, "charset": True}
3595
3596
3597class Exp(Func):
3598    pass
3599
3600
3601class Explode(Func):
3602    pass
3603
3604
3605class ExponentialTimeDecayedAvg(AggFunc):
3606    arg_types = {"this": True, "time": False, "decay": False}
3607
3608
3609class Floor(Func):
3610    arg_types = {"this": True, "decimals": False}
3611
3612
3613class Greatest(Func):
3614    arg_types = {"this": True, "expressions": False}
3615    is_var_len_args = True
3616
3617
3618class GroupConcat(Func):
3619    arg_types = {"this": True, "separator": False}
3620
3621
3622class GroupUniqArray(AggFunc):
3623    arg_types = {"this": True, "size": False}
3624
3625
3626class Hex(Func):
3627    pass
3628
3629
3630class Histogram(AggFunc):
3631    arg_types = {"this": True, "bins": False}
3632
3633
3634class If(Func):
3635    arg_types = {"this": True, "true": True, "false": False}
3636
3637
3638class IfNull(Func):
3639    arg_types = {"this": True, "expression": False}
3640    _sql_names = ["IFNULL", "NVL"]
3641
3642
3643class Initcap(Func):
3644    pass
3645
3646
3647class JSONKeyValue(Expression):
3648    arg_types = {"this": True, "expression": True}
3649
3650
3651class JSONObject(Func):
3652    arg_types = {
3653        "expressions": False,
3654        "null_handling": False,
3655        "unique_keys": False,
3656        "return_type": False,
3657        "format_json": False,
3658        "encoding": False,
3659    }
3660
3661
3662class JSONBContains(Binary):
3663    _sql_names = ["JSONB_CONTAINS"]
3664
3665
3666class JSONExtract(Binary, Func):
3667    _sql_names = ["JSON_EXTRACT"]
3668
3669
3670class JSONExtractScalar(JSONExtract):
3671    _sql_names = ["JSON_EXTRACT_SCALAR"]
3672
3673
3674class JSONBExtract(JSONExtract):
3675    _sql_names = ["JSONB_EXTRACT"]
3676
3677
3678class JSONBExtractScalar(JSONExtract):
3679    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3680
3681
3682class JSONFormat(Func):
3683    arg_types = {"this": False, "options": False}
3684    _sql_names = ["JSON_FORMAT"]
3685
3686
3687class Least(Func):
3688    arg_types = {"expressions": False}
3689    is_var_len_args = True
3690
3691
3692class Length(Func):
3693    pass
3694
3695
3696class Levenshtein(Func):
3697    arg_types = {
3698        "this": True,
3699        "expression": False,
3700        "ins_cost": False,
3701        "del_cost": False,
3702        "sub_cost": False,
3703    }
3704
3705
3706class Ln(Func):
3707    pass
3708
3709
3710class Log(Func):
3711    arg_types = {"this": True, "expression": False}
3712
3713
3714class Log2(Func):
3715    pass
3716
3717
3718class Log10(Func):
3719    pass
3720
3721
3722class LogicalOr(AggFunc):
3723    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
3724
3725
3726class LogicalAnd(AggFunc):
3727    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
3728
3729
3730class Lower(Func):
3731    _sql_names = ["LOWER", "LCASE"]
3732
3733
3734class Map(Func):
3735    arg_types = {"keys": False, "values": False}
3736
3737
3738class VarMap(Func):
3739    arg_types = {"keys": True, "values": True}
3740    is_var_len_args = True
3741
3742
3743# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
3744class MatchAgainst(Func):
3745    arg_types = {"this": True, "expressions": True, "modifier": False}
3746
3747
3748class Max(AggFunc):
3749    arg_types = {"this": True, "expressions": False}
3750    is_var_len_args = True
3751
3752
3753class Min(AggFunc):
3754    arg_types = {"this": True, "expressions": False}
3755    is_var_len_args = True
3756
3757
3758class Month(Func):
3759    pass
3760
3761
3762class Nvl2(Func):
3763    arg_types = {"this": True, "true": True, "false": False}
3764
3765
3766class Posexplode(Func):
3767    pass
3768
3769
3770class Pow(Binary, Func):
3771    _sql_names = ["POWER", "POW"]
3772
3773
3774class PercentileCont(AggFunc):
3775    pass
3776
3777
3778class PercentileDisc(AggFunc):
3779    pass
3780
3781
3782class Quantile(AggFunc):
3783    arg_types = {"this": True, "quantile": True}
3784
3785
3786# Clickhouse-specific:
3787# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3788class Quantiles(AggFunc):
3789    arg_types = {"parameters": True, "expressions": True}
3790    is_var_len_args = True
3791
3792
3793class QuantileIf(AggFunc):
3794    arg_types = {"parameters": True, "expressions": True}
3795
3796
3797class ApproxQuantile(Quantile):
3798    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3799
3800
3801class RangeN(Func):
3802    arg_types = {"this": True, "expressions": True, "each": False}
3803
3804
3805class ReadCSV(Func):
3806    _sql_names = ["READ_CSV"]
3807    is_var_len_args = True
3808    arg_types = {"this": True, "expressions": False}
3809
3810
3811class Reduce(Func):
3812    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3813
3814
3815class RegexpExtract(Func):
3816    arg_types = {
3817        "this": True,
3818        "expression": True,
3819        "position": False,
3820        "occurrence": False,
3821        "group": False,
3822    }
3823
3824
3825class RegexpLike(Func):
3826    arg_types = {"this": True, "expression": True, "flag": False}
3827
3828
3829class RegexpILike(Func):
3830    arg_types = {"this": True, "expression": True, "flag": False}
3831
3832
3833# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
3834# limit is the number of times a pattern is applied
3835class RegexpSplit(Func):
3836    arg_types = {"this": True, "expression": True, "limit": False}
3837
3838
3839class Repeat(Func):
3840    arg_types = {"this": True, "times": True}
3841
3842
3843class Round(Func):
3844    arg_types = {"this": True, "decimals": False}
3845
3846
3847class RowNumber(Func):
3848    arg_types: t.Dict[str, t.Any] = {}
3849
3850
3851class SafeDivide(Func):
3852    arg_types = {"this": True, "expression": True}
3853
3854
3855class SetAgg(AggFunc):
3856    pass
3857
3858
3859class SortArray(Func):
3860    arg_types = {"this": True, "asc": False}
3861
3862
3863class Split(Func):
3864    arg_types = {"this": True, "expression": True, "limit": False}
3865
3866
3867# Start may be omitted in the case of postgres
3868# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3869class Substring(Func):
3870    arg_types = {"this": True, "start": False, "length": False}
3871
3872
3873class StrPosition(Func):
3874    arg_types = {
3875        "this": True,
3876        "substr": True,
3877        "position": False,
3878        "instance": False,
3879    }
3880
3881
3882class StrToDate(Func):
3883    arg_types = {"this": True, "format": True}
3884
3885
3886class StrToTime(Func):
3887    arg_types = {"this": True, "format": True}
3888
3889
3890# Spark allows unix_timestamp()
3891# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3892class StrToUnix(Func):
3893    arg_types = {"this": False, "format": False}
3894
3895
3896class NumberToStr(Func):
3897    arg_types = {"this": True, "format": True}
3898
3899
3900class Struct(Func):
3901    arg_types = {"expressions": True}
3902    is_var_len_args = True
3903
3904
3905class StructExtract(Func):
3906    arg_types = {"this": True, "expression": True}
3907
3908
3909class Sum(AggFunc):
3910    pass
3911
3912
3913class Sqrt(Func):
3914    pass
3915
3916
3917class Stddev(AggFunc):
3918    pass
3919
3920
3921class StddevPop(AggFunc):
3922    pass
3923
3924
3925class StddevSamp(AggFunc):
3926    pass
3927
3928
3929class TimeToStr(Func):
3930    arg_types = {"this": True, "format": True}
3931
3932
3933class TimeToTimeStr(Func):
3934    pass
3935
3936
3937class TimeToUnix(Func):
3938    pass
3939
3940
3941class TimeStrToDate(Func):
3942    pass
3943
3944
3945class TimeStrToTime(Func):
3946    pass
3947
3948
3949class TimeStrToUnix(Func):
3950    pass
3951
3952
3953class Trim(Func):
3954    arg_types = {
3955        "this": True,
3956        "expression": False,
3957        "position": False,
3958        "collation": False,
3959    }
3960
3961
3962class TsOrDsAdd(Func, TimeUnit):
3963    arg_types = {"this": True, "expression": True, "unit": False}
3964
3965
3966class TsOrDsToDateStr(Func):
3967    pass
3968
3969
3970class TsOrDsToDate(Func):
3971    arg_types = {"this": True, "format": False}
3972
3973
3974class TsOrDiToDi(Func):
3975    pass
3976
3977
3978class Unhex(Func):
3979    pass
3980
3981
3982class UnixToStr(Func):
3983    arg_types = {"this": True, "format": False}
3984
3985
3986# https://prestodb.io/docs/current/functions/datetime.html
3987# presto has weird zone/hours/minutes
3988class UnixToTime(Func):
3989    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3990
3991    SECONDS = Literal.string("seconds")
3992    MILLIS = Literal.string("millis")
3993    MICROS = Literal.string("micros")
3994
3995
3996class UnixToTimeStr(Func):
3997    pass
3998
3999
4000class Upper(Func):
4001    _sql_names = ["UPPER", "UCASE"]
4002
4003
4004class Variance(AggFunc):
4005    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4006
4007
4008class VariancePop(AggFunc):
4009    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4010
4011
4012class Week(Func):
4013    arg_types = {"this": True, "mode": False}
4014
4015
4016class XMLTable(Func):
4017    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
4018
4019
4020class Year(Func):
4021    pass
4022
4023
4024class Use(Expression):
4025    arg_types = {"this": True, "kind": False}
4026
4027
4028class Merge(Expression):
4029    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
4030
4031
4032class When(Func):
4033    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
4034
4035
4036def _norm_arg(arg):
4037    return arg.lower() if type(arg) is str else arg
4038
4039
4040ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
4041
4042
4043# Helpers
4044def maybe_parse(
4045    sql_or_expression: ExpOrStr,
4046    *,
4047    into: t.Optional[IntoType] = None,
4048    dialect: DialectType = None,
4049    prefix: t.Optional[str] = None,
4050    copy: bool = False,
4051    **opts,
4052) -> Expression:
4053    """Gracefully handle a possible string or expression.
4054
4055    Example:
4056        >>> maybe_parse("1")
4057        (LITERAL this: 1, is_string: False)
4058        >>> maybe_parse(to_identifier("x"))
4059        (IDENTIFIER this: x, quoted: False)
4060
4061    Args:
4062        sql_or_expression: the SQL code string or an expression
4063        into: the SQLGlot Expression to parse into
4064        dialect: the dialect used to parse the input expressions (in the case that an
4065            input expression is a SQL string).
4066        prefix: a string to prefix the sql with before it gets parsed
4067            (automatically includes a space)
4068        copy: whether or not to copy the expression.
4069        **opts: other options to use to parse the input expressions (again, in the case
4070            that an input expression is a SQL string).
4071
4072    Returns:
4073        Expression: the parsed or given expression.
4074    """
4075    if isinstance(sql_or_expression, Expression):
4076        if copy:
4077            return sql_or_expression.copy()
4078        return sql_or_expression
4079
4080    import sqlglot
4081
4082    sql = str(sql_or_expression)
4083    if prefix:
4084        sql = f"{prefix} {sql}"
4085    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
4086
4087
4088def _maybe_copy(instance, copy=True):
4089    return instance.copy() if copy else instance
4090
4091
4092def _is_wrong_expression(expression, into):
4093    return isinstance(expression, Expression) and not isinstance(expression, into)
4094
4095
4096def _apply_builder(
4097    expression,
4098    instance,
4099    arg,
4100    copy=True,
4101    prefix=None,
4102    into=None,
4103    dialect=None,
4104    **opts,
4105):
4106    if _is_wrong_expression(expression, into):
4107        expression = into(this=expression)
4108    instance = _maybe_copy(instance, copy)
4109    expression = maybe_parse(
4110        sql_or_expression=expression,
4111        prefix=prefix,
4112        into=into,
4113        dialect=dialect,
4114        **opts,
4115    )
4116    instance.set(arg, expression)
4117    return instance
4118
4119
4120def _apply_child_list_builder(
4121    *expressions,
4122    instance,
4123    arg,
4124    append=True,
4125    copy=True,
4126    prefix=None,
4127    into=None,
4128    dialect=None,
4129    properties=None,
4130    **opts,
4131):
4132    instance = _maybe_copy(instance, copy)
4133    parsed = []
4134    for expression in expressions:
4135        if _is_wrong_expression(expression, into):
4136            expression = into(expressions=[expression])
4137        expression = maybe_parse(
4138            expression,
4139            into=into,
4140            dialect=dialect,
4141            prefix=prefix,
4142            **opts,
4143        )
4144        parsed.extend(expression.expressions)
4145
4146    existing = instance.args.get(arg)
4147    if append and existing:
4148        parsed = existing.expressions + parsed
4149
4150    child = into(expressions=parsed)
4151    for k, v in (properties or {}).items():
4152        child.set(k, v)
4153    instance.set(arg, child)
4154    return instance
4155
4156
4157def _apply_list_builder(
4158    *expressions,
4159    instance,
4160    arg,
4161    append=True,
4162    copy=True,
4163    prefix=None,
4164    into=None,
4165    dialect=None,
4166    **opts,
4167):
4168    inst = _maybe_copy(instance, copy)
4169
4170    expressions = [
4171        maybe_parse(
4172            sql_or_expression=expression,
4173            into=into,
4174            prefix=prefix,
4175            dialect=dialect,
4176            **opts,
4177        )
4178        for expression in expressions
4179    ]
4180
4181    existing_expressions = inst.args.get(arg)
4182    if append and existing_expressions:
4183        expressions = existing_expressions + expressions
4184
4185    inst.set(arg, expressions)
4186    return inst
4187
4188
4189def _apply_conjunction_builder(
4190    *expressions,
4191    instance,
4192    arg,
4193    into=None,
4194    append=True,
4195    copy=True,
4196    dialect=None,
4197    **opts,
4198):
4199    expressions = [exp for exp in expressions if exp is not None and exp != ""]
4200    if not expressions:
4201        return instance
4202
4203    inst = _maybe_copy(instance, copy)
4204
4205    existing = inst.args.get(arg)
4206    if append and existing is not None:
4207        expressions = [existing.this if into else existing] + list(expressions)
4208
4209    node = and_(*expressions, dialect=dialect, **opts)
4210
4211    inst.set(arg, into(this=node) if into else node)
4212    return inst
4213
4214
4215def _combine(expressions, operator, dialect=None, **opts):
4216    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
4217    this = expressions[0]
4218    if expressions[1:]:
4219        this = _wrap_operator(this)
4220    for expression in expressions[1:]:
4221        this = operator(this=this, expression=_wrap_operator(expression))
4222    return this
4223
4224
4225def _wrap_operator(expression):
4226    if isinstance(expression, (And, Or, Not)):
4227        expression = Paren(this=expression)
4228    return expression
4229
4230
4231def union(left, right, distinct=True, dialect=None, **opts):
4232    """
4233    Initializes a syntax tree from one UNION expression.
4234
4235    Example:
4236        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
4237        'SELECT * FROM foo UNION SELECT * FROM bla'
4238
4239    Args:
4240        left (str | Expression): the SQL code string corresponding to the left-hand side.
4241            If an `Expression` instance is passed, it will be used as-is.
4242        right (str | Expression): the SQL code string corresponding to the right-hand side.
4243            If an `Expression` instance is passed, it will be used as-is.
4244        distinct (bool): set the DISTINCT flag if and only if this is true.
4245        dialect (str): the dialect used to parse the input expression.
4246        opts (kwargs): other options to use to parse the input expressions.
4247    Returns:
4248        Union: the syntax tree for the UNION expression.
4249    """
4250    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4251    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4252
4253    return Union(this=left, expression=right, distinct=distinct)
4254
4255
4256def intersect(left, right, distinct=True, dialect=None, **opts):
4257    """
4258    Initializes a syntax tree from one INTERSECT expression.
4259
4260    Example:
4261        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4262        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4263
4264    Args:
4265        left (str | Expression): the SQL code string corresponding to the left-hand side.
4266            If an `Expression` instance is passed, it will be used as-is.
4267        right (str | Expression): the SQL code string corresponding to the right-hand side.
4268            If an `Expression` instance is passed, it will be used as-is.
4269        distinct (bool): set the DISTINCT flag if and only if this is true.
4270        dialect (str): the dialect used to parse the input expression.
4271        opts (kwargs): other options to use to parse the input expressions.
4272    Returns:
4273        Intersect: the syntax tree for the INTERSECT expression.
4274    """
4275    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4276    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4277
4278    return Intersect(this=left, expression=right, distinct=distinct)
4279
4280
4281def except_(left, right, distinct=True, dialect=None, **opts):
4282    """
4283    Initializes a syntax tree from one EXCEPT expression.
4284
4285    Example:
4286        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4287        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4288
4289    Args:
4290        left (str | Expression): the SQL code string corresponding to the left-hand side.
4291            If an `Expression` instance is passed, it will be used as-is.
4292        right (str | Expression): the SQL code string corresponding to the right-hand side.
4293            If an `Expression` instance is passed, it will be used as-is.
4294        distinct (bool): set the DISTINCT flag if and only if this is true.
4295        dialect (str): the dialect used to parse the input expression.
4296        opts (kwargs): other options to use to parse the input expressions.
4297    Returns:
4298        Except: the syntax tree for the EXCEPT statement.
4299    """
4300    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4301    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4302
4303    return Except(this=left, expression=right, distinct=distinct)
4304
4305
4306def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
4307    """
4308    Initializes a syntax tree from one or multiple SELECT expressions.
4309
4310    Example:
4311        >>> select("col1", "col2").from_("tbl").sql()
4312        'SELECT col1, col2 FROM tbl'
4313
4314    Args:
4315        *expressions: the SQL code string to parse as the expressions of a
4316            SELECT statement. If an Expression instance is passed, this is used as-is.
4317        dialect: the dialect used to parse the input expressions (in the case that an
4318            input expression is a SQL string).
4319        **opts: other options to use to parse the input expressions (again, in the case
4320            that an input expression is a SQL string).
4321
4322    Returns:
4323        Select: the syntax tree for the SELECT statement.
4324    """
4325    return Select().select(*expressions, dialect=dialect, **opts)
4326
4327
4328def from_(*expressions, dialect=None, **opts) -> Select:
4329    """
4330    Initializes a syntax tree from a FROM expression.
4331
4332    Example:
4333        >>> from_("tbl").select("col1", "col2").sql()
4334        'SELECT col1, col2 FROM tbl'
4335
4336    Args:
4337        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4338            SELECT statement. If an Expression instance is passed, this is used as-is.
4339        dialect (str): the dialect used to parse the input expression (in the case that the
4340            input expression is a SQL string).
4341        **opts: other options to use to parse the input expressions (again, in the case
4342            that the input expression is a SQL string).
4343
4344    Returns:
4345        Select: the syntax tree for the SELECT statement.
4346    """
4347    return Select().from_(*expressions, dialect=dialect, **opts)
4348
4349
4350def update(
4351    table: str | Table,
4352    properties: dict,
4353    where: t.Optional[ExpOrStr] = None,
4354    from_: t.Optional[ExpOrStr] = None,
4355    dialect: DialectType = None,
4356    **opts,
4357) -> Update:
4358    """
4359    Creates an update statement.
4360
4361    Example:
4362        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4363        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4364
4365    Args:
4366        *properties: dictionary of properties to set which are
4367            auto converted to sql objects eg None -> NULL
4368        where: sql conditional parsed into a WHERE statement
4369        from_: sql statement parsed into a FROM statement
4370        dialect: the dialect used to parse the input expressions.
4371        **opts: other options to use to parse the input expressions.
4372
4373    Returns:
4374        Update: the syntax tree for the UPDATE statement.
4375    """
4376    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4377    update_expr.set(
4378        "expressions",
4379        [
4380            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4381            for k, v in properties.items()
4382        ],
4383    )
4384    if from_:
4385        update_expr.set(
4386            "from",
4387            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4388        )
4389    if isinstance(where, Condition):
4390        where = Where(this=where)
4391    if where:
4392        update_expr.set(
4393            "where",
4394            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4395        )
4396    return update_expr
4397
4398
4399def delete(
4400    table: ExpOrStr,
4401    where: t.Optional[ExpOrStr] = None,
4402    returning: t.Optional[ExpOrStr] = None,
4403    dialect: DialectType = None,
4404    **opts,
4405) -> Delete:
4406    """
4407    Builds a delete statement.
4408
4409    Example:
4410        >>> delete("my_table", where="id > 1").sql()
4411        'DELETE FROM my_table WHERE id > 1'
4412
4413    Args:
4414        where: sql conditional parsed into a WHERE statement
4415        returning: sql conditional parsed into a RETURNING statement
4416        dialect: the dialect used to parse the input expressions.
4417        **opts: other options to use to parse the input expressions.
4418
4419    Returns:
4420        Delete: the syntax tree for the DELETE statement.
4421    """
4422    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
4423    if where:
4424        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
4425    if returning:
4426        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
4427    return delete_expr
4428
4429
4430def condition(expression, dialect=None, **opts) -> Condition:
4431    """
4432    Initialize a logical condition expression.
4433
4434    Example:
4435        >>> condition("x=1").sql()
4436        'x = 1'
4437
4438        This is helpful for composing larger logical syntax trees:
4439        >>> where = condition("x=1")
4440        >>> where = where.and_("y=1")
4441        >>> Select().from_("tbl").select("*").where(where).sql()
4442        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4443
4444    Args:
4445        *expression (str | Expression): the SQL code string to parse.
4446            If an Expression instance is passed, this is used as-is.
4447        dialect (str): the dialect used to parse the input expression (in the case that the
4448            input expression is a SQL string).
4449        **opts: other options to use to parse the input expressions (again, in the case
4450            that the input expression is a SQL string).
4451
4452    Returns:
4453        Condition: the expression
4454    """
4455    return maybe_parse(  # type: ignore
4456        expression,
4457        into=Condition,
4458        dialect=dialect,
4459        **opts,
4460    )
4461
4462
4463def and_(*expressions, dialect=None, **opts) -> And:
4464    """
4465    Combine multiple conditions with an AND logical operator.
4466
4467    Example:
4468        >>> and_("x=1", and_("y=1", "z=1")).sql()
4469        'x = 1 AND (y = 1 AND z = 1)'
4470
4471    Args:
4472        *expressions (str | Expression): the SQL code strings to parse.
4473            If an Expression instance is passed, this is used as-is.
4474        dialect (str): the dialect used to parse the input expression.
4475        **opts: other options to use to parse the input expressions.
4476
4477    Returns:
4478        And: the new condition
4479    """
4480    return _combine(expressions, And, dialect, **opts)
4481
4482
4483def or_(*expressions, dialect=None, **opts) -> Or:
4484    """
4485    Combine multiple conditions with an OR logical operator.
4486
4487    Example:
4488        >>> or_("x=1", or_("y=1", "z=1")).sql()
4489        'x = 1 OR (y = 1 OR z = 1)'
4490
4491    Args:
4492        *expressions (str | Expression): the SQL code strings to parse.
4493            If an Expression instance is passed, this is used as-is.
4494        dialect (str): the dialect used to parse the input expression.
4495        **opts: other options to use to parse the input expressions.
4496
4497    Returns:
4498        Or: the new condition
4499    """
4500    return _combine(expressions, Or, dialect, **opts)
4501
4502
4503def not_(expression, dialect=None, **opts) -> Not:
4504    """
4505    Wrap a condition with a NOT operator.
4506
4507    Example:
4508        >>> not_("this_suit='black'").sql()
4509        "NOT this_suit = 'black'"
4510
4511    Args:
4512        expression (str | Expression): the SQL code strings to parse.
4513            If an Expression instance is passed, this is used as-is.
4514        dialect (str): the dialect used to parse the input expression.
4515        **opts: other options to use to parse the input expressions.
4516
4517    Returns:
4518        Not: the new condition
4519    """
4520    this = condition(
4521        expression,
4522        dialect=dialect,
4523        **opts,
4524    )
4525    return Not(this=_wrap_operator(this))
4526
4527
4528def paren(expression) -> Paren:
4529    return Paren(this=expression)
4530
4531
4532SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4533
4534
4535@t.overload
4536def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4537    ...
4538
4539
4540@t.overload
4541def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4542    ...
4543
4544
4545def to_identifier(name, quoted=None):
4546    """Builds an identifier.
4547
4548    Args:
4549        name: The name to turn into an identifier.
4550        quoted: Whether or not force quote the identifier.
4551
4552    Returns:
4553        The identifier ast node.
4554    """
4555
4556    if name is None:
4557        return None
4558
4559    if isinstance(name, Identifier):
4560        identifier = name
4561    elif isinstance(name, str):
4562        identifier = Identifier(
4563            this=name,
4564            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
4565        )
4566    else:
4567        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4568    return identifier
4569
4570
4571INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4572
4573
4574def to_interval(interval: str | Literal) -> Interval:
4575    """Builds an interval expression from a string like '1 day' or '5 months'."""
4576    if isinstance(interval, Literal):
4577        if not interval.is_string:
4578            raise ValueError("Invalid interval string.")
4579
4580        interval = interval.this
4581
4582    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4583
4584    if not interval_parts:
4585        raise ValueError("Invalid interval string.")
4586
4587    return Interval(
4588        this=Literal.string(interval_parts.group(1)),
4589        unit=Var(this=interval_parts.group(2)),
4590    )
4591
4592
4593@t.overload
4594def to_table(sql_path: str | Table, **kwargs) -> Table:
4595    ...
4596
4597
4598@t.overload
4599def to_table(sql_path: None, **kwargs) -> None:
4600    ...
4601
4602
4603def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4604    """
4605    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4606    If a table is passed in then that table is returned.
4607
4608    Args:
4609        sql_path: a `[catalog].[schema].[table]` string.
4610
4611    Returns:
4612        A table expression.
4613    """
4614    if sql_path is None or isinstance(sql_path, Table):
4615        return sql_path
4616    if not isinstance(sql_path, str):
4617        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4618
4619    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4620    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4621
4622
4623def to_column(sql_path: str | Column, **kwargs) -> Column:
4624    """
4625    Create a column from a `[table].[column]` sql path. Schema is optional.
4626
4627    If a column is passed in then that column is returned.
4628
4629    Args:
4630        sql_path: `[table].[column]` string
4631    Returns:
4632        Table: A column expression
4633    """
4634    if sql_path is None or isinstance(sql_path, Column):
4635        return sql_path
4636    if not isinstance(sql_path, str):
4637        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4638    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
4639
4640
4641def alias_(
4642    expression: ExpOrStr,
4643    alias: str | Identifier,
4644    table: bool | t.Sequence[str | Identifier] = False,
4645    quoted: t.Optional[bool] = None,
4646    dialect: DialectType = None,
4647    **opts,
4648):
4649    """Create an Alias expression.
4650
4651    Example:
4652        >>> alias_('foo', 'bar').sql()
4653        'foo AS bar'
4654
4655        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4656        '(SELECT 1, 2) AS bar(a, b)'
4657
4658    Args:
4659        expression: the SQL code strings to parse.
4660            If an Expression instance is passed, this is used as-is.
4661        alias: the alias name to use. If the name has
4662            special characters it is quoted.
4663        table: Whether or not to create a table alias, can also be a list of columns.
4664        quoted: whether or not to quote the alias
4665        dialect: the dialect used to parse the input expression.
4666        **opts: other options to use to parse the input expressions.
4667
4668    Returns:
4669        Alias: the aliased expression
4670    """
4671    exp = maybe_parse(expression, dialect=dialect, **opts)
4672    alias = to_identifier(alias, quoted=quoted)
4673
4674    if table:
4675        table_alias = TableAlias(this=alias)
4676        exp.set("alias", table_alias)
4677
4678        if not isinstance(table, bool):
4679            for column in table:
4680                table_alias.append("columns", to_identifier(column, quoted=quoted))
4681
4682        return exp
4683
4684    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4685    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4686    # for the complete Window expression.
4687    #
4688    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4689
4690    if "alias" in exp.arg_types and not isinstance(exp, Window):
4691        exp = exp.copy()
4692        exp.set("alias", alias)
4693        return exp
4694    return Alias(this=exp, alias=alias)
4695
4696
4697def subquery(expression, alias=None, dialect=None, **opts):
4698    """
4699    Build a subquery expression.
4700
4701    Example:
4702        >>> subquery('select x from tbl', 'bar').select('x').sql()
4703        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4704
4705    Args:
4706        expression (str | Expression): the SQL code strings to parse.
4707            If an Expression instance is passed, this is used as-is.
4708        alias (str | Expression): the alias name to use.
4709        dialect (str): the dialect used to parse the input expression.
4710        **opts: other options to use to parse the input expressions.
4711
4712    Returns:
4713        Select: a new select with the subquery expression included
4714    """
4715
4716    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4717    return Select().from_(expression, dialect=dialect, **opts)
4718
4719
4720def column(
4721    col: str | Identifier,
4722    table: t.Optional[str | Identifier] = None,
4723    db: t.Optional[str | Identifier] = None,
4724    catalog: t.Optional[str | Identifier] = None,
4725    quoted: t.Optional[bool] = None,
4726) -> Column:
4727    """
4728    Build a Column.
4729
4730    Args:
4731        col: column name
4732        table: table name
4733        db: db name
4734        catalog: catalog name
4735        quoted: whether or not to force quote each part
4736    Returns:
4737        Column: column instance
4738    """
4739    return Column(
4740        this=to_identifier(col, quoted=quoted),
4741        table=to_identifier(table, quoted=quoted),
4742        db=to_identifier(db, quoted=quoted),
4743        catalog=to_identifier(catalog, quoted=quoted),
4744    )
4745
4746
4747def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
4748    """Cast an expression to a data type.
4749
4750    Example:
4751        >>> cast('x + 1', 'int').sql()
4752        'CAST(x + 1 AS INT)'
4753
4754    Args:
4755        expression: The expression to cast.
4756        to: The datatype to cast to.
4757
4758    Returns:
4759        A cast node.
4760    """
4761    expression = maybe_parse(expression, **opts)
4762    return Cast(this=expression, to=DataType.build(to, **opts))
4763
4764
4765def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4766    """Build a Table.
4767
4768    Args:
4769        table (str | Expression): column name
4770        db (str | Expression): db name
4771        catalog (str | Expression): catalog name
4772
4773    Returns:
4774        Table: table instance
4775    """
4776    return Table(
4777        this=to_identifier(table, quoted=quoted),
4778        db=to_identifier(db, quoted=quoted),
4779        catalog=to_identifier(catalog, quoted=quoted),
4780        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4781    )
4782
4783
4784def values(
4785    values: t.Iterable[t.Tuple[t.Any, ...]],
4786    alias: t.Optional[str] = None,
4787    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4788) -> Values:
4789    """Build VALUES statement.
4790
4791    Example:
4792        >>> values([(1, '2')]).sql()
4793        "VALUES (1, '2')"
4794
4795    Args:
4796        values: values statements that will be converted to SQL
4797        alias: optional alias
4798        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4799         If either are provided then an alias is also required.
4800         If a dictionary is provided then the first column of the values will be casted to the expected type
4801         in order to help with type inference.
4802
4803    Returns:
4804        Values: the Values expression object
4805    """
4806    if columns and not alias:
4807        raise ValueError("Alias is required when providing columns")
4808    table_alias = (
4809        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4810        if columns
4811        else TableAlias(this=to_identifier(alias) if alias else None)
4812    )
4813    expressions = [convert(tup) for tup in values]
4814    if columns and isinstance(columns, dict):
4815        types = list(columns.values())
4816        expressions[0].set(
4817            "expressions",
4818            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4819        )
4820    return Values(
4821        expressions=expressions,
4822        alias=table_alias,
4823    )
4824
4825
4826def var(name: t.Optional[ExpOrStr]) -> Var:
4827    """Build a SQL variable.
4828
4829    Example:
4830        >>> repr(var('x'))
4831        '(VAR this: x)'
4832
4833        >>> repr(var(column('x', table='y')))
4834        '(VAR this: x)'
4835
4836    Args:
4837        name: The name of the var or an expression who's name will become the var.
4838
4839    Returns:
4840        The new variable node.
4841    """
4842    if not name:
4843        raise ValueError("Cannot convert empty name into var.")
4844
4845    if isinstance(name, Expression):
4846        name = name.name
4847    return Var(this=name)
4848
4849
4850def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4851    """Build ALTER TABLE... RENAME... expression
4852
4853    Args:
4854        old_name: The old name of the table
4855        new_name: The new name of the table
4856
4857    Returns:
4858        Alter table expression
4859    """
4860    old_table = to_table(old_name)
4861    new_table = to_table(new_name)
4862    return AlterTable(
4863        this=old_table,
4864        actions=[
4865            RenameTable(this=new_table),
4866        ],
4867    )
4868
4869
4870def convert(value) -> Expression:
4871    """Convert a python value into an expression object.
4872
4873    Raises an error if a conversion is not possible.
4874
4875    Args:
4876        value (Any): a python object
4877
4878    Returns:
4879        Expression: the equivalent expression object
4880    """
4881    if isinstance(value, Expression):
4882        return value
4883    if value is None:
4884        return NULL
4885    if isinstance(value, bool):
4886        return Boolean(this=value)
4887    if isinstance(value, str):
4888        return Literal.string(value)
4889    if isinstance(value, float) and math.isnan(value):
4890        return NULL
4891    if isinstance(value, numbers.Number):
4892        return Literal.number(value)
4893    if isinstance(value, tuple):
4894        return Tuple(expressions=[convert(v) for v in value])
4895    if isinstance(value, list):
4896        return Array(expressions=[convert(v) for v in value])
4897    if isinstance(value, dict):
4898        return Map(
4899            keys=[convert(k) for k in value],
4900            values=[convert(v) for v in value.values()],
4901        )
4902    if isinstance(value, datetime.datetime):
4903        datetime_literal = Literal.string(
4904            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4905        )
4906        return TimeStrToTime(this=datetime_literal)
4907    if isinstance(value, datetime.date):
4908        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4909        return DateStrToDate(this=date_literal)
4910    raise ValueError(f"Cannot convert {value}")
4911
4912
4913def replace_children(expression, fun, *args, **kwargs):
4914    """
4915    Replace children of an expression with the result of a lambda fun(child) -> exp.
4916    """
4917    for k, v in expression.args.items():
4918        is_list_arg = type(v) is list
4919
4920        child_nodes = v if is_list_arg else [v]
4921        new_child_nodes = []
4922
4923        for cn in child_nodes:
4924            if isinstance(cn, Expression):
4925                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
4926                    new_child_nodes.append(child_node)
4927                    child_node.parent = expression
4928                    child_node.arg_key = k
4929            else:
4930                new_child_nodes.append(cn)
4931
4932        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4933
4934
4935def column_table_names(expression):
4936    """
4937    Return all table names referenced through columns in an expression.
4938
4939    Example:
4940        >>> import sqlglot
4941        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4942        ['c', 'a']
4943
4944    Args:
4945        expression (sqlglot.Expression): expression to find table names
4946
4947    Returns:
4948        list: A list of unique names
4949    """
4950    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4951
4952
4953def table_name(table) -> str:
4954    """Get the full name of a table as a string.
4955
4956    Args:
4957        table (exp.Table | str): table expression node or string.
4958
4959    Examples:
4960        >>> from sqlglot import exp, parse_one
4961        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4962        'a.b.c'
4963
4964    Returns:
4965        The table name.
4966    """
4967
4968    table = maybe_parse(table, into=Table)
4969
4970    if not table:
4971        raise ValueError(f"Cannot parse {table}")
4972
4973    return ".".join(
4974        part
4975        for part in (
4976            table.text("catalog"),
4977            table.text("db"),
4978            table.name,
4979        )
4980        if part
4981    )
4982
4983
4984def replace_tables(expression, mapping):
4985    """Replace all tables in expression according to the mapping.
4986
4987    Args:
4988        expression (sqlglot.Expression): expression node to be transformed and replaced.
4989        mapping (Dict[str, str]): mapping of table names.
4990
4991    Examples:
4992        >>> from sqlglot import exp, parse_one
4993        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4994        'SELECT * FROM c'
4995
4996    Returns:
4997        The mapped expression.
4998    """
4999
5000    def _replace_tables(node):
5001        if isinstance(node, Table):
5002            new_name = mapping.get(table_name(node))
5003            if new_name:
5004                return to_table(
5005                    new_name,
5006                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
5007                )
5008        return node
5009
5010    return expression.transform(_replace_tables)
5011
5012
5013def replace_placeholders(expression, *args, **kwargs):
5014    """Replace placeholders in an expression.
5015
5016    Args:
5017        expression (sqlglot.Expression): expression node to be transformed and replaced.
5018        args: positional names that will substitute unnamed placeholders in the given order.
5019        kwargs: keyword arguments that will substitute named placeholders.
5020
5021    Examples:
5022        >>> from sqlglot import exp, parse_one
5023        >>> replace_placeholders(
5024        ...     parse_one("select * from :tbl where ? = ?"),
5025        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
5026        ... ).sql()
5027        "SELECT * FROM foo WHERE str_col = 'b'"
5028
5029    Returns:
5030        The mapped expression.
5031    """
5032
5033    def _replace_placeholders(node, args, **kwargs):
5034        if isinstance(node, Placeholder):
5035            if node.name:
5036                new_name = kwargs.get(node.name)
5037                if new_name:
5038                    return convert(new_name)
5039            else:
5040                try:
5041                    return convert(next(args))
5042                except StopIteration:
5043                    pass
5044        return node
5045
5046    return expression.transform(_replace_placeholders, iter(args), **kwargs)
5047
5048
5049def expand(
5050    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
5051) -> Expression:
5052    """Transforms an expression by expanding all referenced sources into subqueries.
5053
5054    Examples:
5055        >>> from sqlglot import parse_one
5056        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
5057        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
5058
5059        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
5060        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
5061
5062    Args:
5063        expression: The expression to expand.
5064        sources: A dictionary of name to Subqueryables.
5065        copy: Whether or not to copy the expression during transformation. Defaults to True.
5066
5067    Returns:
5068        The transformed expression.
5069    """
5070
5071    def _expand(node: Expression):
5072        if isinstance(node, Table):
5073            name = table_name(node)
5074            source = sources.get(name)
5075            if source:
5076                subquery = source.subquery(node.alias or name)
5077                subquery.comments = [f"source: {name}"]
5078                return subquery.transform(_expand, copy=False)
5079        return node
5080
5081    return expression.transform(_expand, copy=copy)
5082
5083
5084def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
5085    """
5086    Returns a Func expression.
5087
5088    Examples:
5089        >>> func("abs", 5).sql()
5090        'ABS(5)'
5091
5092        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
5093        'CAST(5 AS DOUBLE)'
5094
5095    Args:
5096        name: the name of the function to build.
5097        args: the args used to instantiate the function of interest.
5098        dialect: the source dialect.
5099        kwargs: the kwargs used to instantiate the function of interest.
5100
5101    Note:
5102        The arguments `args` and `kwargs` are mutually exclusive.
5103
5104    Returns:
5105        An instance of the function of interest, or an anonymous function, if `name` doesn't
5106        correspond to an existing `sqlglot.expressions.Func` class.
5107    """
5108    if args and kwargs:
5109        raise ValueError("Can't use both args and kwargs to instantiate a function.")
5110
5111    from sqlglot.dialects.dialect import Dialect
5112
5113    converted = [convert(arg) for arg in args]
5114    kwargs = {key: convert(value) for key, value in kwargs.items()}
5115
5116    parser = Dialect.get_or_raise(dialect)().parser()
5117    from_args_list = parser.FUNCTIONS.get(name.upper())
5118
5119    if from_args_list:
5120        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
5121    else:
5122        kwargs = kwargs or {"expressions": converted}
5123        function = Anonymous(this=name, **kwargs)
5124
5125    for error_message in function.error_messages(converted):
5126        raise ValueError(error_message)
5127
5128    return function
5129
5130
5131def true():
5132    """
5133    Returns a true Boolean expression.
5134    """
5135    return Boolean(this=True)
5136
5137
5138def false():
5139    """
5140    Returns a false Boolean expression.
5141    """
5142    return Boolean(this=False)
5143
5144
5145def null():
5146    """
5147    Returns a Null expression.
5148    """
5149    return Null()
5150
5151
5152# TODO: deprecate this
5153TRUE = Boolean(this=True)
5154FALSE = Boolean(this=False)
5155NULL = Null()